Commissions Based on Tiered Sales

[bgame32]

Hi Everyone! This has taken me much longer than it probably should have, but I am so stumped. I am trying to automate a pay sheet rather than manually calculating pay based on commission.
Commission is as follows:
$0-575 - 60%
$575.01-$850-70%
$850.01+ - 90%

I have worked on calculating based on the running cumulative sales, but continue to run into an error. I would like the sales to automatically separate into each tier as appropriate. However, I cannot write a formula to automatically calculate that.

If I could not separate into tiers, then I would like to just having a running commission based on cumulative sales.

This could be right in front of my face, but I have looked at it too long.

Any and ALL suggestions would be SO appreciated.

THANK YOU!

 

[Peter Bartholomew]

Gurus: I have no expectation that this should be the solution offered to the OP. Please contribute something more appropriate.
However, by chance, I was working on some advanced Lambda functions developed to perform first in / first out (FIFO) allocations that link outputs (sales) back to inputs (purchase batches. This Excel 365 workbook applies the functions to this banded commission problem.

= LET(
  commission, Allocateλ(salesAmount,Bands, Rate),
  REDUCE(0,commission,Addλ))

 

[Peter Bartholomew]

I was rather hoping someone would step in with a traditional Excel solution.
Basically, your layout of helper cells is correct and should allow you to handle the calculation.
For any element of the calculation you need to know both the threshold and the limit of the tier as well as the cumulative sales both before and after the service. The overlap is then the maximum lower bound subtracted from the minimum upper bound. If there is no overlap, the formula will return a negative value which is discarded (set to zero).