Thread: Sliding scale formula ----Difficult----

I need a formula for a sliding scale commission. I have figured it out in separate steps but not in one nice neat formula. The sliding scale is 40% on the 1st $100, 30% on the 2nd $100, 25% on the 3rd $100, 20% on the 4th $100, and 15% on anything over $400. Example: If something sold for $580 the commission would be: $40 of the 1st $100, $30 of the second $100, $25 of the 3rd $100, $20 of the 4th $100, and $27 of the remaining $180 after $400.

What I would like is:
1st cell= what the sale was for
2nd cell=what the commission is
3rd cell=what the remainder is

Please, please, please help me.

Thanks
Daniel

If the value of the sale is in cell A1 then the formula to calculate commission is:

=MIN(40,A1*0.4)+MAX(0,MIN(30,(A1-100)*0.3))+MAX(0,MIN(25,(A1-200)*0.25)+MAX(0,MIN(20,(A1-300)*0.2))+MAX(0,(A1-400)*0.15))

If you put that formula in cell B1 then in C1 you could put:

=A1-B1

To give you the remainder.