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?