Tiered Commission Calculation Table

[Showell22]

Hello!

My company has a tiered commission schedule, and we would like to be able to put it into a working Excel book so when we change our values, the commission amounts update with the total sales.

I attached a copy of the book, I kind of got it going using a lot of "IF" formulas, but anything over the $500,000 range I begin to run into issues.

The range is the following:
From 0-150,000.00 you get 27.27% of that amount
From $150,001.00-350,000.00 you get 33.33% of that amount
From $350,001.00-500,000.00 you get 43.43% of that amount
From $500,001.00- Up you get 45.45% of that amount

So for example, if a person made $600,000 in sales, they would have made:

0-150,000.00 you get 27.27% of that amount ---->40,905.00
150-350 ----> 66,659.67
350-500 ----> 65,145.00
500-Up----> 45,450.00

Thank you for the help!!

[JohnTopley]

See attached

=SUMPRODUCT(($E$2>=$A$2:$A$5)*($E$2-$A$2:$A$5),C$2:C$5)