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

 

[nlitedesign]

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

Hi Mike,

Thanks very much for your reply, the equation makes sense I didn't think about that route. I got Sum Product stuck in my head.

I very much appreciate your time.

Thank you.

 

[Dave Patton]

"What is happening where you changed the percentage to " -0.0025 " "

Try reviewing the formula shown in C4; you can use Excel's Formula Evaluate.
The formula uses the rate increment for each tier.
" Is there a way you entering the exact % on the right hand side of the array? "
N.B. With this formula the cell above the rates (C6) is blank.

Commission2020.xlsm
	A	B	C
1	50,556.89	Number
2		Calculated
3		Calculated	213.89
4
5		Bracket [rB]	Rate
6
7		0	1.00%
8		5,000	0.75%
9		10,000	0.50%
10		20,000	0.25%
11
Commission

Cell Formulas
Range	Formula
C3	C3	=SUMPRODUCT(--(A1>B7:B10),A1-B7:B10,C7:C10-C6:C9)

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