 # Bonus Calculation with multiple targets

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?

4 Replies

# Re: Bonus Calculation with multiple targets

See the attached workbook.

# Re: Bonus Calculation with multiple targets

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))) # Re: Bonus Calculation with multiple targets

@khofreiter And another way to do it. See attached. # Re: Bonus Calculation with multiple targets

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