Tiered Pricing Formula Using Table

O

osscie3

Board Regular
Joined
Apr 30, 2014
Messages
70
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[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Threshold[/TD]
[TD]Fee[/TD]
[TD]Diff. Rate[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]100,000[/TD]
[TD]0[/TD]
[TD]0.035[/TD]
[TD]0.035[/TD]
[/TR]
[TR]
[TD]100,001[/TD]
[TD]250,000[/TD]
[TD]100,000[/TD]
[TD]0.025[/TD]
[TD]-0.010[/TD]
[/TR]
[TR]
[TD]250,001[/TD]
[TD]500,000[/TD]
[TD]250,000[/TD]
[TD]0.015[/TD]
[TD]-0.010[/TD]
[/TR]
[TR]
[TD]500,001[/TD]
[TD]1,000,000[/TD]
[TD]500,000[/TD]
[TD]0.005[/TD]
[TD]-0.010[/TD]
[/TR]
[TR]
[TD]1,000,001[/TD]
[TD][/TD]
[TD]1,000,001[/TD]
[TD]0[/TD]
[TD]-0.005[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Does this return what you want?


Excel 2013/2016
ABCDEFG
1MinMaxThresholdFeeDiff. Rate
20100,00000.0350.035430,970
3100,001250,000100,0000.025-0.01
4250,001500,000250,0000.015-0.0110,965
5500,0011,000,000500,0000.005-0.01
61,000,0011,000,0010-0.005
Sheet1
Cell Formulas
RangeFormula
G4=SUMPRODUCT(FREQUENCY(ROW(INDIRECT("1:"&G2)),SUBTOTAL(9,OFFSET(B2,0,0,1+ROW(B2:B5)-ROW(B2)))),D2:D6)
 
Upvote 0
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 :) )
 
Upvote 0

Excel 2010
ABCDJ
19,960.50
2430,700.00
3
4MinFeeDiff. Rate
500.0350.035
6100,0000.025-0.01
7250,0000.015-0.01
8500,0000.005-0.01
91,000,0000-0.005
10
11100,000.003,500.00
12150,000.003,750.00
13180,700.002,710.50
14430,700.009,960.50
1a
Cell Formulas
RangeFormula
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)
 
Upvote 0
With your layout
=SUMPRODUCT(--(J2>C2:C6),J2-C2:C6,E2:E6) gives 9960.50


Excel 2010
ABCDEIJ
1MinMaxThresholdFeeDiff. Rate
20100,00000.0350.035430700
3100,001250,000100,0000.025-0.01
4250,001500,000250,0000.015-0.01
5500,0011,000,000500,0000.005-0.01
61,000,0011,000,0010-0.005
7
89,960.50
9
10100,000.000.0353,500.00
11150,000.000.0253,750.00
12180,700.000.0152,710.50
13430,700.009,960.50
1b
Cell Formulas
RangeFormula
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)
 
Last edited:
Upvote 0
osscie3 said:
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[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Min
[/TD]
[TD]Max
[/TD]
[TD]Threshold
[/TD]
[TD]Fee
[/TD]
[TD]Diff. Rate
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100,000
[/TD]
[TD][/TD]
[TD]0.035
[/TD]
[TD]0.035
[/TD]
[/TR]
[TR]
[TD]100,001
[/TD]
[TD]250,000
[/TD]
[TD]100,000
[/TD]
[TD]0.025
[/TD]
[TD]-0.010
[/TD]
[/TR]
[TR]
[TD]250,001
[/TD]
[TD]500,000
[/TD]
[TD]250,000
[/TD]
[TD]0.015
[/TD]
[TD]-0.010
[/TD]
[/TR]
[TR]
[TD]500,001
[/TD]
[TD]1,000,000
[/TD]
[TD]500,000
[/TD]
[TD]0.005
[/TD]
[TD]-0.010
[/TD]
[/TR]
[TR]
[TD]1,000,001
[/TD]
[TD][/TD]
[TD]1,000,001
[/TD]
[TD][/TD]
[TD]-0.005
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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
ABCDEFGHIJ
1MinMaxThresholdFeeDiff. Rate
20100,00000.0350.0351000500
3100,001250,000100,0000.025-0.0113500
4250,001500,000250,0000.015-0.01
5500,0011,000,000500,0000.005-0.01
61,000,0011,000,0000-0.005
7
8MinMaxThresholdFeeDiff. Rate
90100,00000.0350.0351000500
10100,001250,000100,0000.025-0.0113500.005
11250,001500,000250,0000.015-0.01
12500,0011,000,000500,0000.005-0.01
131,000,0011,000,0010-0.005
Sheet2
Cell Formulas
RangeFormula
J3=SUMPRODUCT(--(J2>C2:C6), J2-C2:C6, E2:E6)
J10=SUMPRODUCT(--(J9>C9:C13), J9-C9:C13, E9:E13)
 
Last edited:
Upvote 0
I have severe cataracts. My two posts had an incorrect amount in J2.
With J2 = 430,970 formulas show 9,964.55
 
Upvote 0
jtakw said:
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
ABCDEFGHIJ
MinMaxThresholdFeeDiff. Rate
MinMaxThresholdFeeDiff. Rate

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.035[/TD]
[TD="align: right"]0.035[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000500[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]100,001[/TD]
[TD="align: right"]250,000[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]0.025[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13500[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]250,001[/TD]
[TD="align: right"]500,000[/TD]
[TD="align: right"]250,000[/TD]
[TD="align: right"]0.015[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]500,001[/TD]
[TD="align: right"]1,000,000[/TD]
[TD="align: right"]500,000[/TD]
[TD="align: right"]0.005[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1,000,001[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1,000,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.005[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.035[/TD]
[TD="align: right"]0.035[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1000500[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]100,001[/TD]
[TD="align: right"]250,000[/TD]
[TD="align: right"]100,000[/TD]
[TD="align: right"]0.025[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13500.005[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]250,001[/TD]
[TD="align: right"]500,000[/TD]
[TD="align: right"]250,000[/TD]
[TD="align: right"]0.015[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]500,001[/TD]
[TD="align: right"]1,000,000[/TD]
[TD="align: right"]500,000[/TD]
[TD="align: right"]0.005[/TD]
[TD="align: right"]-0.01[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1,000,001[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1,000,001[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]-0.005[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J3[/TH]
[TD="align: left"]=SUMPRODUCT(--(J2>C2:C6), J2-C2:C6, E2:E6)[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J10[/TH]
[TD="align: left"]=SUMPRODUCT(--(J9>C9:C13), J9-C9:C13, E9:E13)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
NEED AN EQUATION FOR FINDING THE VALUE
Replies
5
Views
413
Peter_SSs
Peter_SSs
J
  • Question Question
Check Qualification based on Multiple Criteria Using a Table with Logic Operators
Replies
0
Views
390
jdutle
J
A
  • Question Question
Sliding scale commission formula
Replies
6
Views
9K
Dave Patton
D
K
  • Question Question
Sumproduct multi-tiered thresholds
2
Replies
12
Views
3K
Dave Patton
D
S
  • Question Question
Calculating Commission based on tiers
Replies
2
Views
675
Dave Patton
D

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top