Forum Discussion

Jen Kuntz's avatar
Aug 14, 2018
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%. 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

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze 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

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor
    if 1.0 = 50% and 2.0 = 100% why is 3.0 not 150%?
    how was 140% computed?
    thanks
    • Jen Kuntz's avatar
      Jen Kuntz
      MVP
      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 Chan's avatar
        Man Fai Chan
        Iron 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. 

Resources