Link to home
Start Free TrialLog in
Avatar of jtrusler
jtrusler

asked on

Nested if formula for bonus determination

I have a sheet that tracks the income of commission employees. It is calculated monthly. the employees have a commission of "x" until they reach 25k of commissions. When the commission rises to 35K it increases to "y". at 40K it increase to "z" . at 50K it increases to "a" finally at 60K, it increases to "b". the higher level commission is earned only after the employees surpass the various thresholds. IE, they don't earn the higher commission if a comission earned at a lower threshold crosses an upper threshold.. This accumulates during the year and then resets anew at the beginning of the next year. I need a formula that will look at the income in cell B^ and calculate the commission earned. Then in C6 onward, it will add B6 to C6 to see if the higher commission has been achieved. This keeps going to the end of the year.
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

can you upload a worksheet with your data layout and a few rows of sample data? At the moment it's unclear where you want what.
You can calculate the commission earned with something like
=LOOKUP(B6,{0,25000,30000,40000,50000,60000},{5,10,15,20,25,30})
but to be more specific with a solution it would sure help to see your spreadsheet and have the "x" and "y" translated into numbers.
 
Avatar of jtrusler
jtrusler

ASKER

Good point. Let me put that together. I am trying to avoid a lookup table and just evaluate the entered numbers. Let me remove names and post the site. Let me see if your solution might work. THANK YOU
This is the first quarter
Book2.xlsx
care to throw in a few words what you would like to see in which cells based on what logic?
Here is what I used which works for me.
 
=IF(D24<=25000,0.5,IF(AND(D24>25000,D24<=50000),0.55,IF(AND(D24>50000,D24<=100000),0.6,IF(D24>100000,0.65))))
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial