Calculating Tiered Bonus Based on File Count - HELP ME PLEASE

I am trying to work a formula to that pays an additional flat amount per file.

The bonus structure works like this.
Capture Rate Bonus
Tier 1- 0-40% $20/file
Tier 2 41%-59% $30/file
Tier 3 60% + $40/file

The problem is that I don’t want the bonus retroactive but determined in tiers. Right now the spread sheet pays the bonus retroactively. For example in December the calculations state that there were 29 closed TC files with a capture rate of 58% so that is in Tier 2 ($30 x 29 files)=$870.

It actually should be 20 files closed in Tier 1 ($20 x 20 files)=$400 and 9 files closed in Tier 2 ($30 x 9)= $270 for a total of $670 bonus in December. Make sense?

So the calculations have to determine how many closed TC files each month were closed in each tier and then calculate the amount paid in each tier and then added to the bonus which shows the base salary plus the bonus in an hourly rate.

Here is the workbook. the highlighted columns are the ones with the data we are working with.

Thanks!!!

Well that's what makes it tricky. 20 is dynamic, the tiers are capped based on the capture percentage, which is calculated based off how many files are in each month. The example given in the December numbers, 20 represents how many files were closed to reach 40% as soon as the 21st file was closed the percentage goes to 42% and there for that file and each thereafter are $30 a file vs the previous 20 files (40% and less) which are $20 a file.

Does that make more sense?

Precisely!