Tiered Pricing Formula Using Table

[osscie3]

Hi all, I've got a table that I'm referring to for a tiered pricing formula. I've read through as many threads as I can count but can't seem to find the answer to my issue. My formula is always a thousand off my manual calculation.

My formula is - =SUMPRODUCT(--(J2>C2:C6), J2-C2:C6, E2:E6)

J2 = 430,970

Formula returns - 9,964.55
Manual calculation returns - 10,964,55 (100,000@0.035 (3,500) + 250,000@0.025 (6250) + 80,970@0.015 (1214.55))

Any ideas?

My table is as follows:
A B C D E

Min	Max	Threshold	Fee	Diff. Rate
0	100,000	0	0.035	0.035
100,001	250,000	100,000	0.025	-0.010
250,001	500,000	250,000	0.015	-0.010
500,001	1,000,000	500,000	0.005	-0.010
1,000,001		1,000,001	0	-0.005

<tbody>
</tbody>

 

[VBA Geek]

Does this return what you want?

Excel 2013/2016
	A	B	C	D	E	F	G
1	Min	Max	Threshold	Fee	Diff. Rate
2	0	100,000	0	0.035	0.035		430,970
3	100,001	250,000	100,000	0.025	-0.01
4	250,001	500,000	250,000	0.015	-0.01		10,965
5	500,001	1,000,000	500,000	0.005	-0.01
6	1,000,001		1,000,001	0	-0.005
Sheet1

Cell Formulas
Range	Formula
G4	=SUMPRODUCT(FREQUENCY(ROW(INDIRECT("1:"&G2)),SUBTOTAL(9,OFFSET(B2,0,0,1+ROW(B2:B5)-ROW(B2)))),D2:D6)

 

[Special-K99]

I can't see why it produces 9964.55 either.

I was using

=SUMPRODUCT((J2>C2:C^)*(J2-C2:C6)*(E2:E6)

and that too produces 9964.55

(I really only replied to this so it would subscribe me as I wanted to see what the solution was )

 

[Dave Patton]

Excel 2010
	A	B	C	D	J
1			9,960.50
2					430,700.00
3
4	Min	Fee	Diff. Rate
5	0	0.035	0.035
6	100,000	0.025	-0.01
7	250,000	0.015	-0.01
8	500,000	0.005	-0.01
9	1,000,000	0	-0.005
10
11	100,000.00	3,500.00
12	150,000.00	3,750.00
13	180,700.00	2,710.50
14	430,700.00	9,960.50
1a

Cell Formulas
Range	Formula
C1	= SUMPRODUCT(--(J2>$A$5:$A$9),J2-$A$5:$A$9,C5:C9)
C5	=B5-N(B4)
B11	=B5*A11
B12	=B6*A12
B13	=B7*A13
A12	=A7-A6
A13	=J2-SUM(A11:A12)

 

[Dave Patton]

With your layout
=SUMPRODUCT(--(J2>C2:C6),J2-C2:C6,E2:E6) gives 9960.50

Excel 2010
	A	B	C	D	E	I	J
1	Min	Max	Threshold	Fee	Diff. Rate
2	0	100,000	0	0.035	0.035		430700
3	100,001	250,000	100,000	0.025	-0.01
4	250,001	500,000	250,000	0.015	-0.01
5	500,001	1,000,000	500,000	0.005	-0.01
6	1,000,001		1,000,001	0	-0.005
7
8					9,960.50
9
10		100,000.00	0.035	3,500.00
11		150,000.00	0.025	3,750.00
12		180,700.00	0.015	2,710.50
13		430,700.00		9,960.50
1b

Cell Formulas
Range	Formula
E8	=SUMPRODUCT(--(J2>C2:C6),J2-C2:C6,E2:E6)
B13	=SUM(B10:B12)
B10	=C3
B11	=C4-C3
B12	=J2-SUM(B10:B11)
C10	=D2
C11	=D3
C12	=D4
D10	=B10*C10
D11	=B11*C11
D12	=B12*C12
D13	=SUM(D10:D12)

 

[jtakw]

Hi all, I've got a table that I'm referring to for a tiered pricing formula. I've read through as many threads as I can count but can't seem to find the answer to my issue. My formula is always a thousand off my manual calculation.

My formula is - =SUMPRODUCT(--(J2>C2:C6), J2-C2:C6, E2:E6)

J2 = 430,970

Formula returns - 9,964.55
Manual calculation returns - 10,964,55 (100,000@0.035 (3,500) + 250,000@0.025 (6250) + 80,970@0.015 (1214.55))

Any ideas?

My table is as follows:
A B C D E

Min	Max	Threshold	Fee	Diff. Rate
	100,000		0.035	0.035
100,001	250,000	100,000	0.025	-0.010
250,001	500,000	250,000	0.015	-0.010
500,001	1,000,000	500,000	0.005	-0.010
1,000,001		1,000,001		-0.005

<tbody>
</tbody>

Hi,

You guys are chasing a ghost, there's nothing wrong with the result here, it is Correct at 9964.55.

Highlighted in Bold Red above, OP's Manual calculation is Wrong, it should be (100000 @ .035 = 3500) + (150000 @ .025 = 3750) + (180970 @ .015) = 2714.55, so Total Is 9964.55

However, I would change C6 to 1000000 (instead of 1000001) because anything over 1000000 will be off by .005

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Min	Max	Threshold	Fee	Diff. Rate
2	0	100,000	0	0.035	0.035					1000500
3	100,001	250,000	100,000	0.025	-0.01					13500
4	250,001	500,000	250,000	0.015	-0.01
5	500,001	1,000,000	500,000	0.005	-0.01
6	1,000,001		1,000,000	0	-0.005
7
8	Min	Max	Threshold	Fee	Diff. Rate
9	0	100,000	0	0.035	0.035					1000500
10	100,001	250,000	100,000	0.025	-0.01					13500.005
11	250,001	500,000	250,000	0.015	-0.01
12	500,001	1,000,000	500,000	0.005	-0.01
13	1,000,001		1,000,001	0	-0.005
Sheet2

Cell Formulas
Range	Formula
J3	=SUMPRODUCT(--(J2>C2:C6), J2-C2:C6, E2:E6)
J10	=SUMPRODUCT(--(J9>C9:C13), J9-C9:C13, E9:E13)

 

[Dave Patton]

I have severe cataracts. My two posts had an incorrect amount in J2.
With J2 = 430,970 formulas show 9,964.55

 

[osscie3]

Hi,

You guys are chasing a ghost, there's nothing wrong with the result here, it is Correct at 9964.55.

Highlighted in Bold Red above, OP's Manual calculation is Wrong, it should be (100000 @ .035 = 3500) + (150000 @ .025 = 3750) + (180970 @ .015) = 2714.55, so Total Is 9964.55

However, I would change C6 to 1000000 (instead of 1000001) because anything over 1000000 will be off by .005

Excel 2010

	A	B	C	D	E	F	G	H	I	J
1	Min	Max	Threshold	Fee	Diff. Rate
2	0	100,000	0	0.035	0.035					1000500
3	100,001	250,000	100,000	0.025	-0.01					13500
4	250,001	500,000	250,000	0.015	-0.01
5	500,001	1,000,000	500,000	0.005	-0.01
6	1,000,001		1,000,000	0	-0.005
7
8	Min	Max	Threshold	Fee	Diff. Rate
9	0	100,000	0	0.035	0.035					1000500
10	100,001	250,000	100,000	0.025	-0.01					13500.005
11	250,001	500,000	250,000	0.015	-0.01
12	500,001	1,000,000	500,000	0.005	-0.01
13	1,000,001		1,000,001	0	-0.005

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula J3 =SUMPRODUCT(--(J2>C2:C6), J2-C2:C6, E2:E6) J10 =SUMPRODUCT(--(J9>C9:C13), J9-C9:C13, E9:E13) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

You're exactly right. This is why I love this forum. More sets of eyes is better than only mine. It should NOT be 250,000 * 0.025 as 250k is just the threshold. I had a feeling I was manually calculating it wrong but I just couldn't see it. Thanks so much.

 

[jtakw]

You're welcome, I started off trying to dissect your Table and Formula looking for what might have been causing the error, and could not find anything. While verifying numbers step by step, that's when I realized the mistake with your manual calculation..., it's happened to all of us...

Anyway, I still recommend changing C6 (in my sample table) from 1000001 to 1000000