Think of the formula this way:
You pay 3% for everything.
For everything over $500, you pay an additional 2%
For everything over $750, you pay an additional 2.5%
Those deltas are the differential rates from one tier to the next. Ignoring the $10 adder,
A B C D 1 Amt Rate Delta 2 $0.00 3.0% 3.0%C2: =B2-N(B1) 3 $500.00 5.0% 2.0% 4 $750.00 7.5% 2.5% 5 6 Amt Comm 7 $300.00 $9.00B6: =SUMPRODUCT((A6 > $A$2:$A$4) * (A6 - $A$2:$A$4) * $C$2:$C$4) 8 $400.00 $12.00 9 $500.00 $15.00 10 $600.00 $20.00 11 $700.00 $25.00 12 $800.00 $31.25 13 $900.00 $38.75
Bookmarks