Points based on goal & weight of measures

[Mayte]

Hi-

I have no idea is this can be done as a marco but .... wondering if anybody
can give me any suggestions??

I need to do a ranking for my managers. I had a simple ranking:
actual/goal=score and then a raking from 1 to 5 but now my director added a
weight to each measure and that sort threw me off. I honestly went blank
because can't figure out the points using the weight for each measure ....any
ideas??

column-A, measures (all are unique, no duplicates)
column-B, goals (some are greater than and some less than)
column-C, weight for each measure
column-D, actual results
column-E, will have the points scored
column-F, will be the ranking based on the points

A B C D E F
Measure Goal Weight Actual Points Ranking
M-1 >= 85% 45% 45%
M-2 >= 90% 25% 25%
M-3 <= 25% 5% 5%
M-4 >= 95% 10% 10%
M-5 <= 7% 15% 15%

Thanks,
Mayte

 

[aamerrasheed via OfficeKB.com]

Hi,
Can you please clarify one thing. For M3 and M5, points should be higher if
the actual is less. How to decide the points in this case.
For example for M3 you say, <=25 percent. Now if the actual is 0% or 25% or
50 %, what points do you want to give?

 

[aamerrasheed via OfficeKB.com]

Hi,

You can do like this. You'll get the points and then based on that you can
assign ranks.
In this case, if all goals are met, means actual are same as goal%, sum of
these 5 point values will be 1.

Meas Goal Goal% Weight Actual Points
M-1 >= 85 45 70 =IF(B2=">=", E2/(C2*100)*D2, C2/(E2*100)*D2)
M-2 >= 90 25 60 =IF(B3=">=",E3/(C3*100)*D3,C3/(E3*100)*D3)
M-3 <= 25 5 40 =IF(B4=">=",E4/(C4*100)*D4,C4/(E4*100)*D4)
M-4 >= 95 10 70 =IF(B5=">=",E5/(C5*100)*D5,C5/(E5*100)*D5)
M-5 <= 7 15 10 =IF(B6=">=",E6/(C6*100)*D6,C6/(E6*100)*D6)

Hi,
Can you please clarify one thing. For M3 and M5, points should be higher if
the actual is less. How to decide the points in this case.
For example for M3 you say, <=25 percent. Now if the actual is 0% or 25% or
50 %, what points do you want to give?

Hi-

[quoted text clipped - 24 lines]

Thanks,
Mayte

 

[Shane Devenshire]

Hi,

Since I'm not clear on the stuff at the bottom, here is something your might
start witn

Here is how you do a weighted average without VBA where the Column B
contains the values and Column A contains the weights.
=SUMPRODUCT(A2:A9*B2:B9)/SUM(B2:B9)

And here is a conditional weighted average
=SUMPRODUCT(--(C2:C9=K2:K9),A2:A9*B2:B9)/SUMPRODUCT(B2:B9*(C2:C9=K2:K9))
In this case the condition is the C2=K2 and so on..

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Mayte]

this helps a lot ..thanks!