tiered bonus

Hi everyone,
I am trying to create something in excel that will help calculate a tiered bonus structure with a fixed bonus pool. Here is an example:

The Board determines there will be a bonus pool of $20,000. There are 20 employees with an average of (3.4 – 3.99), and 4 employees with an average of (4 – 4.79) and 1 employee with an average of (4.8 - 5). This equals a total of 25 employees eligible to share in the bonus pool. Each employee in the respective groups would receive the following bonus:

20 Employees: (3.4 – 3.99) = $780.94
4 Employees: (4 – 4.79) = $859.03
1 Employees: (4.8 - 5) = $944.93


I have an IF statement to determine which pool people are in now I am having trouble getting the calculations to match the bonus pool. Any ideas?

Does anyone know if there is a way to calculate without knowing the bonus amounts? The bonus amounts were manually calculated and it would be nice if there was a formula for it.

Basically it was done by backing into the numbers and we came within 15 cents of the $20,000. It would be great if there was a calculation that would calculate the base and the extra percentages but I'm not sure how to do that.

The bonus appears to tiered as follows:

=C1+C2*1.1+C3*1.21 i.e tiers increment by 10%

where C1=Number of employees in Tier1, C2=Number in Tier2, c3=Number in Tier3

Using above we get result of 25.61 (in H1 .. see below)

To allocate bonus:

use

in I1

=ROUNDUP(C1*(1+0.1) ^(ROWS($1:1)-1)/$H$1*$G$1,2)

where h1=25.16
g1=20000

Copy formula down to I3

Thanks, I will try that and update the thread if it works as expected.