Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Points based on goal & weight of measures
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Points based on goal & weight of measures
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? Mayte wrote: 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 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200901/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Points based on goal & weight of measures
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) aamerrasheed wrote: 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 -- Message posted via http://www.officekb.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Points based on goal & weight of measures
this helps a lot ..thanks!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for points if less than goal | Excel Discussion (Misc queries) | |||
Help making postage calculator based on a given weight | Excel Discussion (Misc queries) | |||
how to limit the Weight of any weight distribution | Excel Discussion (Misc queries) | |||
Adding weight measures | Excel Worksheet Functions | |||
Calculate needed gallons of water, based on weight of person | Excel Worksheet Functions |