Points based on goal & weight of measures
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" wrote in message
...
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
|