Points based on goal & weight of measures

M

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
 
S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top