Excel spreadsheet to calculate bonuses paid out to employees

[pianaholic]

Hi all! New to excel so this may be something very simple to formulate but it's not something I am introduced to yet. My boss pays out a bonus to his two employees. One is part time and the other is full time. He is looking for a simple spreadsheet where he can punch in his expected annual revenue and be able to have the worksheet show him how much he needs to pay to each employee. I can do most of the spreadsheet on my own but am not educated on one particular part.

We have a tiered bonus system where revenue between $400K and $500K is paid out at 10%, $500K and $600K is paid out at 15%, and $600K and above is paid at 20%. What I can't figure out how to do (with my limited excel knowledge) is to cap the two lower tiers to only $100K each if revenue is above $600K. I have attached a basic spreadsheet for reference. I really would like him to only have to put a number in the yellow box and have it do the rest of the work for him. I need to know what function or formula would be used to calculate the boxes in green. Thanks in advance for all the help!

[Excelko]

Hello pianaholic,
you can do it simply with IF function, but you need to understand clearly what you want.
I think question is if bonus of 10% is from total revenue or just Revenue-400k $ amount and for 15% if it is again from total revenue or just from amount above 500k$?
I added both scenarios to your file, so you can choose what you want. If formula is not doing what you know just let me know, but be more specific

Hope this helped.

[pianaholic]

Thanks. I actually did figure that out earlier and tried to post a reply, but I think it didn't like me posting my answers directly in the thread rather than updating the excel document. Thanks for the reply!