Forum Discussion
Jen Kuntz
Aug 14, 2018MVP
How to calculate a weighting with this scenario?
I'm working on a formula for bonus calculation (i.e. incentive program).
Ratings for bonuses are calculated with something like this, a 2.0 is 100% bonus, a 1.0 rating is 50% and a 3.0 is 140%. The ratings though are not just 1, 2 or 3, they are 2.4 or 1.8 etc. and they want a weighted average kind of result. If someone was rated as a 1.5, they would get 75% for instance, and 2.5 would get 120% and any result in between should essentially get a % appropriate for its place in the continuum of those weights.
Rating - Weight (using dash to indicate column separator)
1.0 - 50%
2.0 - 100%
3.0 - 140%
Manually I am working through it like this, for a 1.8 rating as an example:
- 1.0 is 50%
- take the rating minus the rounded down rating (1.8 - 1.0) to get the differential (0.8)
- multiply the differential by the difference between the rounded down rating and rounded up rating (100% minus 50% = 50% x 0.8 = 40%)
- the weighting on 1.8 would then be 90%.
- same calc on 2.4 would result in 116% (100% + .4 of 40%).
It seems like there is way cleaner ways to get at the same logic. Thoughts? This just seems too clunky and I'm pretty sure there is an easier way!
The function VLOOKUP is helpful in this case.
First of all, I prepared the lookup table in Column A to Column C. The value in Column C is used for calculating decimal part of weight.
In order to find the decimal part, I used the formula F2 - INT(F2).
I am not sure if the rating can be less than 1 or greater than 3. But, I think the idea is similar.
9 Replies
Sort By
- Lorenzo KimBronze Contributor
Ms. Kuntz
I don't know if I am doing it right.
please see attached file, if this can help.
you can fill any number - say, in column D- 1.43 or in column J- 2.11 ...
thanks
- Looks like you've got it too, thanks.
- Lorenzo KimBronze Contributorif 1.0 = 50% and 2.0 = 100% why is 3.0 not 150%?
how was 140% computed?
thanks- The 140% isn't computed, it's a given static value for a rating of 3.0. Basically, you really are getting docked if you're underperforming and not quite compensated at the same ratio if you are a 3.0 rating.
- Man Fai ChanIron Contributor
The function VLOOKUP is helpful in this case.
First of all, I prepared the lookup table in Column A to Column C. The value in Column C is used for calculating decimal part of weight.
In order to find the decimal part, I used the formula F2 - INT(F2).
I am not sure if the rating can be less than 1 or greater than 3. But, I think the idea is similar.