Tiered Fee Formula Help

Originally Posted by shg

If you want to use the stucture in the OP,

	G	H	I
20	$ 750.00	$ 37.50	H20: =SUMPRODUCT((G20 > {0,500,750}) * (G20 - {0,500,750}) * ({3,5,7.5}% - {0,3,5}%)) + 10

The last term is the differential rate between tiers.

Could you explain the last term to me, {3,5,7.5}% - {0,3,5}, for example of my rates were 2.7% and 7.5% how would that translate? I guess what I'm asking for is what is the last term or how do I calculate it.

Originally Posted by shg

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.