I am looking for help with a formula that will calculate the following:
Tiered Bonus Structure
For amounts ranging from $7 Million to $16 Million they receive $5,000 per million
For amounts ranging from $17 Million to $27 Million they receive $2,500 per million
For amounts ranging $27 Million + they receive $1,000 per million
=sumproduct((floor.math(e2,$e$1)>=$a$2:$a$5)*(floor.math(e2,$e$1)-$b$2:$b$5)*$c$2:$c$5)/$e$1
here is how I would probably approach it. sales amounts in col A, vlookup in col B and a reference table in cols F and G. Once you start the pattern for your bonuses you can drag down until you get to the next tier, then change it (for example from 16 M to 17 M it goes from increments of 5 k to 2.5 k) then drag down until the next increment change, then repeat to as high as you need. This is the vlookup formula I used =VLOOKUP(A2,$F$2:$G$51,2,TRUE), keep in mind the bonus table has to be in ascending order but the sales area does not. BTW, Tim and I do not get the same results for 17.5 M so one of us may be misunderstanding (and it could be me, wouldn't be the first time).
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
This query has been cross-posted here: https://www.mrexcel.com/forum/excel-...red-bonus.html
Please note that, in future, you must disclose cross-posting yourself - please see the forum rules.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks