Tiered Fee Formula Help

Hope all is well. I'm trying to calculate a simple tiered commission formula. The base fee would be ($100) + (3% on the first $500) + (5% on the next $250 or $501-$750) + (7.5% on anything above the $750 threshold). When I do the calc manually on $750 the fee should be $37.50. I can't seem to figure out how to make this work and keeping getting $4.50.

Sale Price: $750
Base Fee: $10
Fee $0 to $500 ($500*3%) = $15
Fee $500 to $750 ($250*5%) = $12.50

Total fee = $37.50

Using (SUMPRODUCT(--(G20>{0;500;750}),--(G20-{0;500;750}), {0.03;0.05;0.075}))+10 gets me $45.00

Any help would greatly be appreciated.

Try

=10+(MIN(500,F1)*0.03+MAX(0,750-500)*0.05)+MAX(0,F1-750)*0.075

OR

=SUMPRODUCT(--(G20>{0;500;750}),--(G20-{0;500;750}), {0.03;0.02;0.025})+10

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.

You're welcome.

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

Look at C2:C4:

C2 is 3% - 0% (0% is the rate for the non-existent 0th tier)

C3 is 5% - 3% (3% is the rate for the first tier)

C3 is 7.5% - 5% (5% is the rate for the second tier)

It is the difference between tiers. I don't think I can explain it any other way.

You're welcome.