Sum Product Array Issue

[nlitedesign]

Hello All,

Could someone please point out where my problem is within the following equation.

=SUMPRODUCT(--(A1>{0;5000;10000;20000}), (A1-{0;5000;10000;20000}), {0.01;0.0075;0.005;0.0025})

The equation is to work out a fee on a tabled scale (as follows):

Value	Fee
£0 > £5,000	1.00%
£5001 > £10,000	0.75%
£10,001 > £20,000	0.50%
£20,001 and Over	0.25%

The correct answer is £213.89 but for some reason I keep getting £1126.422 as the answer...

I know it is probably something obvious, but i just cant see it now..

Thanks in advance

Keep Safe.

Phil

 

[mikerickson]

What is the data that gives you the incorrect answer?

 

[nlitedesign]

My apologies.. yes you are correct i missed out the vital number... 50556.89

 

[mikerickson]

In the example you have given, you are applying a fee based on all of the categories.
If A1 = 50556.89 it passes all of the tests (A1>{0;5000;10000;20000}) and your formula is calculating the sum of all possible fees.

 

[nlitedesign]

In the example you have given, you are applying a fee based on all of the categories.
If A1 = 50556.89 it passes all of the tests (A1>{0;5000;10000;20000}) and your formula is calculating the sum of all possible fees.

Yes that is correct.. for the first £5000 I pay 1%, then £5001 to £10000 I pay 0.75% and so on.. this adds up to £213.89.. Well it should... but I keep getting the higher £1126 answer...

 

[mikerickson]

The problem is that formula is paying 1+.75+.5+.25 on the first 5000, that first 5000 is getting into all four bins.

Try
=MIN(A1,5000)*0.01+ MAX(0,MIN(10000,A1-5000))*0.0075 + MAX(0,MIN(20000,A1-10000))*0.0005 + MAX(0,A1-20000)*0.0025

 

[Dave Patton]

=SUMPRODUCT(--(A1>{0;5000;10000;20000}), (A1-{0;5000;10000;20000}), {0.01;-0.0025;-0.0025;-0.0025})

Check the rates

 

[Dave Patton]

Commission2020.xlsm
	C	D	E
1	Number	50,556.89
2	Calculated	213.89
3	as above criteria in formula	213.89
4	as above without named range	213.89
5
6	Bracket [rB]	Rate	Rate Delta [rR]
7	0	1.00%	1.00%
8	5,000	0.75%	-0.25%
9	10,000	0.50%	-0.25%
10	20,000	0.25%	-0.25%
11
Commission

Cell Formulas
Range	Formula
D2	D2	=SUMPRODUCT(--(D1>rB),D1-rB,rR)
D3	D3	=SUMPRODUCT(--(D1>{0;5000;10000;20000}),D1-{0;5000;10000;20000},{0.01;-0.0025;-0.0025;-0.0025})
D4	D4	=SUMPRODUCT(--(D1>C7:C10),D1-C7:C10,E7:E10)
E7:E10	E7	=D7-N(D6)

Named Ranges
Name	Refers To	Cells
rB	=Commission!$C$7:$C$10	D4, D2
rR	=Commission!$E$7:$E$10	D4, D2

 

[Dave Patton]

Commission2020.xlsm
	A	B	C	D
1	50,556.89	Number
2		Calculated	213.89
3		as above criteria in formula	213.89
4		as above without named range	213.89
5		Arithmetic	213.89
6
7		Bracket [rB]	Rate	Rate Delta [rR]
8		0	1.00%	1.00%
9		5,000	0.75%	-0.25%
10		10,000	0.50%	-0.25%
11		20,000	0.25%	-0.25%
Commission

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT(--(A1>rB),A1-rB,rR)
C3	C3	=SUMPRODUCT(--(A1>{0;5000;10000;20000}),A1-{0;5000;10000;20000},{0.01;-0.0025;-0.0025;-0.0025})
C4	C4	=SUMPRODUCT(--(A1>B8:B11),A1-B8:B11,D8:D11)
C5	C5	=MIN(A1,5000)*0.01+ MAX(0,MIN(5000,A1-5000))*0.0075 + MAX(0,MIN(10000,A1-10000))*0.005 + MAX(0,A1-20000)*0.0025
D8:D11	D8	=C8-N(C7)

Named Ranges
Name	Refers To	Cells
rB	=Commission!$B$8:$B$11	C4, C2
rR	=Commission!$D$8:$D$11	C4, C2

 

[nlitedesign]

=SUMPRODUCT(--(A1>{0;5000;10000;20000}), (A1-{0;5000;10000;20000}), {0.01;-0.0025;-0.0025;-0.0025})

Check the rates

Hi Dave,

Thank you very much for your help, it is much appreciated.

That is great its works.. I think I understand a majority of the formula, the only question I do have is..
What is happening where you changed the percentage to " -0.0025 ". The reason why I ask is that I have a fe of these fee tables to work with, and the are not all as easy as a 1/4 of a percent difference each drop. When i first researched this formula with regards to stamp duty in the UK, it had the exact values on each side of the array. Is there a way you entering the exact % on the right hand side of the array?

Thanks again