Feb 23 2022 02:04 PM
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!
Feb 23 2022 02:51 PM
See the attached workbook.
Feb 23 2022 03:18 PM
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)))
Feb 23 2022 09:40 PM
Feb 24 2022 06:44 AM
Thank you everyone for your suggestions! This is very helpful. @Riny_van_Eekelen @mathetes @Hans Vogelaar