Forum Discussion
Solved
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%. T...
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.
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.
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.
- wow! great work Mr. Chan
- Thank you Man Fai Chan... that's similar to my thinking. I used TRUNC and MOD to get the whole/decimal portion, same type of thinking as you.
