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.00 |
|
B6: =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