Hi,

I am usually quite good at working out how to bend excel formulas to my will but i feel like I am head-butting a brick wall here...

Basically I want to make a table to work out bonus payments for a group of people. There are 4 levels: No bonus, Target, High, & Excellence.
He have a specific amount of money put into the pot that is determined by the amount of people in it.

I want to create formulas to break down the amount paid so it is always the same regardless of the amount of people in the pot.

No bonus - 0
Target - one amount
High - 25% higher than Target
Excellence - 100 more than target.


Is it possible to formalize this because i always find that we go over if there are more on the higher end because I cant get the formulas to change depending on each other without circular references...

Link to the file I am playing with: https://www.dropbox.com/s/psvslrghwe...onus.xlsx?dl=0

Thanks in advance for any ideas/thoughts/help!


Chris