Quote Originally Posted by shg View Post
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
H20: =SUMPRODUCT((G20 > {0,500,750}) * (G20 - {0,500,750}) * ({3,5,7.5}% - {0,3,5}%)) + 10

I guess what I'm struggling to grasp are the values in this portion of the formula and what do the numbers mean and what their relation is to the percentages: {0,3,5}%

Sorry for being a pain.