Nested if formula for bonus determination

jtrusler
jtrusler used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
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.
 

Author

Commented:
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

Author

Commented:
This is the first quarter
Book2.xlsx
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Most Valuable Expert 2011
Awarded 2010

Commented:
care to throw in a few words what you would like to see in which cells based on what logic?

Author

Commented:
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))))
Most Valuable Expert 2011
Awarded 2010
Commented:
That formula can not easily be derived from the data sample you provided. But aligning the formula in my first comment to the formula you posted above, it would be

=LOOKUP(D24,{0,25001,50001,100001},{0.5,0.55,0.6,0.65})

Does that help?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial