Between 2 numbers

A

Adrac

Active Member
Joined
Feb 13, 2014
Messages
280
Office Version
  1. 365
Platform
  1. Windows
Hello All,

I'm trying to get a formula that will add up different prices.

Cell A1 = 0-5000 = £0.30
Cell A2 = 5001-50,000 = £0.35
Cell A3 = 50,001-75,000 = £0.40
Cell A4 = 75,001 + = £1.25

From the total amount in Cell B1

Is this possible?

Thanks
Adrac
 
Sort by date Sort by votes
This table shows how much each level contributes (in column G)...
[Table="width:, class:grid"][tr][td] [/td][td]
A​
[/td][td]
B​
[/td][td]
C​
[/td][td]
D​
[/td][td]
E​
[/td][td]
F​
[/td][td]
G​
[/td][/tr]
[tr][td]
1​
[/td][td]
0​
[/td][td]
5000​
[/td][td]
0.3​
[/td][td]
1500​
[/td][td][/td][td]
75001​
[/td][td]
1500​
[/td][/tr]

[tr][td]
2​
[/td][td]
5001​
[/td][td]
50000​
[/td][td]
0.35​
[/td][td]
16000​
[/td][td][/td][td]
31501.25​
[/td][td]
16000​
[/td][/tr]

[tr][td]
3​
[/td][td]
50001​
[/td][td]
75000​
[/td][td]
0.4​
[/td][td]
14000​
[/td][td][/td][td][/td][td]
14000​
[/td][/tr]

[tr][td]
4​
[/td][td]
75001​
[/td][td][/td][td]
1.25​
[/td][td][/td][td][/td][td][/td][td]
1.25​
[/td][/tr]

[tr][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
31501.25​
[/td][/tr]
[/table]
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hello Adrac

Did you try the formula that I posted.
I believe that it provides the information that you require.

5000 * .3 = 1500
45000 * .35 = 15750
25000 * .4 = 10000
 
Last edited:
Upvote 0

Excel 2010
BCDEFG
1100,000.0058,500.00BracketRateRate_Delta
258,500.000.000.300.30
35,000.000.350.05
450,000.000.400.05
575,000.001.250.85
1b
Cell Formulas
RangeFormula
C1=SUMPRODUCT(--(B1>E2:E5),B1-E2:E5,G2:G5)
C2=(B1>0)*B1*0.3+(B1>5000)*(B1-5000)*0.05+(B1>50000)*(B1-50000)*0.05+(B1>75000)*(B1-75000)*0.85
G2=F2-N(F1)


Hopefully Adrac will review the posts and advise if any of the suggestions provide the number that he requires.
 
Last edited:
Upvote 0
Sorry for the delay i had to sleep as I'm from the UK and it was 2am lol
Both are very good, and do the same thing, thanks to both of you to reducing my time working this one out. I have gone with Dave's idea only because i have the option in changing the charges and numbers without chnaging each formula.

Thanks again guys.

Adrac
 
Upvote 0
Both are very good, and do the same thing
Click to expand...
No, they don't, they give different results

As I asked before, given 100 000 what would your expected result be?
Mine = 62 750
Dave's = 58 500
[Table="width:, class:grid"][tr][td] [/td][td]
K​
[/td][td]
L​
[/td][td]
M​
[/td][td]
N​
[/td][td]
O​
[/td][td]
P​
[/td][td]
Q​
[/td][/tr]
[tr][td]
1​
[/td][td]
0​
[/td][td]
5000​
[/td][td]
0.3​
[/td][td]
1500​
[/td][td][/td][td]
100000​
[/td][td]
1500​
[/td][/tr]

[tr][td]
2​
[/td][td]
5001​
[/td][td]
50000​
[/td][td]
0.35​
[/td][td]
16000​
[/td][td][/td][td]
62750​
[/td][td]
16000​
[/td][/tr]

[tr][td]
3​
[/td][td]
50001​
[/td][td]
75000​
[/td][td]
0.4​
[/td][td]
14000​
[/td][td][/td][td][/td][td]
14000​
[/td][/tr]

[tr][td]
4​
[/td][td]
75001​
[/td][td][/td][td]
1.25​
[/td][td][/td][td][/td][td][/td][td]
31250​
[/td][/tr]

[tr][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
62750​
[/td][/tr]
[/table]

I have gone with Dave's idea only because i have the option in changing the charges and numbers without chnaging each formula
Click to expand...
Dave's 2nd formula in post #14 hard-codes the values, apart from that, both his other formula and mine reference the values from a table

I also just noticed that part of my formula did not copy across. It should be...
=IF($F$1<=B1,$F$1*C1,D1)+IF(AND($F$1>=A2,$F$1<=B2),($F$1-A2+1)*C2,IF($F$1<=A2,0,D2))+IF(AND($F$1>=A3,$F$1<=B3),($F$1-A3+1)*C3,IF($F$1<=A3,0,D3))+IF(F1<A4,0,($F$1-A4+1)*C4)
 
Upvote 0

Excel 2010
ABCDEFGH
1100,00058,500.00BracketRateRate_DeltaAccumBy Bracket
258,500.000.000.300.300.001,500.00
358,500.005,000.000.350.051,500.0015,750.00
458,500.0050,000.000.400.0517,250.0010,000.00
575,000.001.250.8527,250.0031,250.00
658,500.00
1
Cell Formulas
RangeFormula
F2=E2-N(E1)
H2=(D3-D2)*E2
H6=SUM(H2:H5)
G3=(D3-D2)*E2+G2
B1=SUMPRODUCT(--(A1>rB),A1-rB,rD)
B2=VLOOKUP(A1,rL,4,1)+(A1-VLOOKUP(A1,rL,1,1))*VLOOKUP(A1,rL,2,1)
B3=(A1>0)*A1*F2+(A1>D3)*(A1-D3)*F3+(A1>D4)*(A1-D4)*F4+(A1>D5)*(A1-D5)*F5
B4=IF(A1>D5,G5+(A1-D5)*E5,IF(A1>D4,G4+(A1-D4)*E4,IF(A1>D3,G3+(A1-D3)*E3,IF(A1>0,A1*E2,0))))
Named Ranges
NameRefers ToCells
'1'!rB='1'!$D$2:$D$5
'1'!rD='1'!$F$2:$F$5
'1'!rL='1'!$D$2:$G$5


With certain taxes, commissions, etc., rates are tiered relating to specified brackets. You can use approaches with Boolean logic, if functions, VLookup functions, or SumProduct formulas. Certain calculations such as the Vlookup approach require interim calculations (see Column G). The SumProduct solution is probably the most concise. Use the approach that you prefer. Column H is included to show amount by Bracket.

You can use Excel's Evaluate Formula to visualize how the formula works.

rB named range of Brackets
rD named range of rate Delta
rL named range of the Lookup rate table
N.B. It is not necessary to use Named Ranges.
 
Last edited:
Upvote 0
A refresh to an old post and a test of the current version of XL2BB.

With calculations such as certain taxes, commissions, etc., the rates are tiered relating to specified brackets. Alternative methods of calculation include Boolean logic, if functions, VLookup functions, or SumProduct formulas. Certain calculations such as the Vlookup approach require interim calculations. The SumProduct solution is probably the most concise.
N.B.
1. It is not necessary to use Named Ranges. The heading for the brackets includes [rB] which is the named range for E4:E7
2. If you do not want to have the table on your sheet
a) put the table on another sheet or
b) build the table and convert the ranges to values or
c) build the arrays directly or
d) name the arrays in Name Manager

BigNum is the named range for =9.99999999999999E+30

T202005a.xlsm
BCDEFGH
1T202005a1b
2AmountTax
3100,000.0058,500.00Bracket [rB]RateRate_Delta [rR]Accumulated
458,500.00030%30%0.00
558,500.005,00035%5%1,500.00
650,00040%5%17,250.00
775,000125%85%27,250.00
81.00E+3158,500.00
9
1b
Cell Formulas
RangeFormula
G4G4=F4-N(F3)
C3C3=SUMPRODUCT(--(B3>rB),B3-rB,rR)
C4C4=VLOOKUP(B3,rL,4,1)+(B3-VLOOKUP(B3,rL,1,1))*VLOOKUP(B3,rL,2,1)
C5C5=(B3>0)*B3*G4+(B3>E5)*(B3-E5)*G5+(B3>E6)*(B3-E6)*G6+(B3>E7)*(B3-E7)*G7
E8E8=BigNum
H5:H8H5=MAX(0,(MIN($B$3,E5)-E4))*F4+H4
Named Ranges
NameRefers ToCells
'1b'!rB='1b'!$E$4:$E$7C3:C4, H5
'1b'!rL='1b'!$E$4:$H$7C3:C4, H5
'1b'!rR='1b'!$G$4:$G$7C3:C5
 
Upvote 0
You must log in or register to reply here.

Similar threads

divster27
  • Solved
It's another eBay pricing structure question...
Replies
7
Views
411
StephenCrump
S
A
  • Question Question
Can someone help find the Excel formula
Replies
5
Views
275
Cubist
Cubist
J
  • Solved
My Two Calculators Don't Match
Replies
1
Views
254
Peter_SSs
Peter_SSs
D
  • Question Question
How do you calculate the number of first days of the month have passed between a set date and today
Replies
4
Views
327
damian_r_Home
D
E
  • Solved
Can you add NULL (not Blank "") to an If statement in a formula?
2
Replies
15
Views
1K
MARK858
MARK858

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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