Between 2 numbers

[Adrac]

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

 

[Max1616]

=if(b1<5001,0.3,if(b1<50001,0.35,if(b1<75001,0.4,1.25)))

 

[Adrac]

ah i see, sorry i ment count each 1 = £0.30 so if the Cell B1 had 10 it would equal £3.00 up until 5000 where it would stop counting anything after 5000 in B1 and continue to A2 where it would count from 5001

 

[Dave Patton]

Excel 2010
	B	C	D	E	F	G
1	75,000.00	27,250.00		Bracket	Rate	Rate_Delta
2				0.00	0.30	0.30
3				5,000.00	0.35	0.05
4				50,000.00	0.40	0.05
5				75,000.00	1.25	0.85
1b

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(--(B1>E2:E4),B1-E2:E4,G2:G4)
G2	=F2-N(F1)

 

[FDibbins]

If you are looking for a tiered calculation, this is how I did it (Im sure there is a simpler way to construct this).

1st I made a table with your ranges and rates - and included a column for Max rate...
[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][/tr]
[tr][td]

1​

[/td][td]

0​

[/td][td]

5000​

[/td][td]

0.3​

[/td][td]

1500​

[/td][td][/td][td]

75001​

[/td][/tr]

[tr][td]

2​

[/td][td]

5001​

[/td][td]

50000​

[/td][td]

0.35​

[/td][td]

17500​

[/td][td][/td][td]

49001.25​

[/td][/tr]

[tr][td]

3​

[/td][td]

50001​

[/td][td]

75000​

[/td][td]

0.4​

[/td][td]

30000​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

75001​

[/td][td][/td][td]

1.25​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]
F1=your Qty
F2=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)

 

[Adrac]

Hello Dave, thanks for the reply. Im not sure im explaining myself correctly. I have a driver, he dose a distance between 0 and 5000 miles, he will get paid £0.30 per mile. Then after that he gets paid £0.35 up until 50,000 but i dont want it calculating the 5000 at 0.35 but the tally of miles is in one cell. so the first 5000 miles he gets paid 0.30 then any miles he dose after 5000 he gets paid 0.35 up until 50,000 where he gets 0.45 ...So:
a driver dose the total of 100,000 miles.
in cell B1 will be 100,000 but i want it calculated at the first 5000 at 0.30 and so on? This make sense?

 

[FDibbins]

We posted about the same time, did you see my post?

 

[Adrac]

We posted about the same time, did you see my post?

hey Dibbs yeah but doesn't seem to give me the right figures.

 

[FDibbins]

OK based on 75001 what would your answer be?

 

[FDibbins]

OK I think I see where I slipped up, I had not taken the max from the previous level away from the max of "this" level...
[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][/tr]
[tr][td]

1​

[/td][td]

0​

[/td][td]

5000​

[/td][td]

0.3​

[/td][td]

1500​

[/td][td][/td][td]

75001​

[/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][/tr]

[tr][td]

3​

[/td][td]

50001​

[/td][td]

75000​

[/td][td]

0.4​

[/td][td]

14000​

[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]

75001​

[/td][td][/td][td]

1.25​

[/td][td][/td][td][/td][td][/td][/tr]
[/table]
Same formula

By putting all values into the table, it allows you to change the values without needing to mess with the formula