Bonus Calculation with multiple targets

Copper Contributor

Hello, 

 

I am trying to determine how I can calculate the amount of a bonus. In my example employees can get different bonus amounts based on how much they sell. There are 3 thresholds and corresponding bonus amount. 

 

40-55 apps    $25 per app >=40 and <=55

56-69 apps     $35mper app >=56 and <=69

70+                 $50 per apps >=70

 

I am looking for a formula that will calculate how many apps fall within each threshold. For example if someone has 57 apps how many 40-55 and how many 56-69? The payout would be 16 apps @ $25 each and 2 apps @ $35 each. Total bonus would be $470

 

Is there a formula that can do this? 

 

Thank you for your help!

4 Replies

@khofreiter 

See the attached workbook.

@khofreiter 

 

Here's another route, just to illustrate the fact that in Excel there are often multiple ways to get from Point A to Point B.

Enter the number of apps in the yellow background cell and the bonus appears in the green.

This is the formula: 

=VLOOKUP(B3,BonusTbl,3,1)+(((B3+1)-VLOOKUP(B3,BonusTbl,1,1))*(VLOOKUP(B3,BonusTbl,2,1)))

 

mathetes_0-1645658254179.png

 

@khofreiter And another way to do it. See attached.

Screenshot 2022-02-24 at 06.38.49.png

 

Thank you everyone for your suggestions! This is very helpful. @Riny_van_Eekelen @mathetes @Hans Vogelaar