Conditional formula

[cky09]

Hi everyone,

I've been trying to figure this out without have a ton of formulas and columns, and I'm stuck. Basically, I have a $ figure that is available as a bonus to our employees. I want to distribute that dollar figure based on two criteria:

1. Based on a percentage of each persons' wage base.
2. No more than a cap based on which "tier" a person falls into.

However, when the cap comes into play, I'd like that $ to be redistributed among the remaining people. The problem that keeps cropping up, is that redistribution triggers another cap, and the whole thing needs to cycle and recaclulate again.

I'd like to set up this worksheet, so one could enter the total $ available for bonus, and then the cap for each tier, and the figures would automatically populate. I'm attaching a small scale sample of what I've pieced together so far.

Thanks in advance for any help!

[mubashir aziz]

I have added 3 sheets; Sheet "solution1" with common formulas, Sheet "solution2" after creating a name, Sheet "solution-combined" with few columns ......

[cky09]

This is great - thanks so much!