IF Statement

[Deano]

Hi, I am fairly new at excel and first time using this chat room. I am using
excel 2003 and trying to come up with a formula that will do the following.
Cell A1 = 166570 gallons
I want excel to calculate
0 – 3000 gallons Base Charge $11.52

3000 but <13000 * 0.00087

<13000 * 0.00072
So when I enter 166570 in cell A1 it automatically calculates $130.79 in
cell C1 or lets say just $11.52 in cell C1 if <3000 gallons used or $20.22 in
cell C1 if 10000 gallons were used. Any help would be appreciated.

 

[Bernard Liengme]

Your question is not clear.
How do you calculate by hand, $20.22
How do you calculate by hand $130.79
Please show examples of calculations of $ for
3000 g
4000 g
100,000 g
best wishes

 

[John]

Hi Deano
Your question is not clear and I can't come up with $130.79 but with the formula
below,
it's the closest I can get to what you're asking.
=IF(A1>13000,A1*0.00072+11.52,IF(A1>3000,A1*0.00087+11.52,IF(A1<=3000,11.52)))
I would create a small table and use cell reference instead of numbers.
e.g: =IF(A1>C6,A1*D7+B7,IF(A1>B6,A1*C7+B7,IF(A1<=B6,B7)))
HTH
John

 

[Deano]

Sorry for the misunderstanding, here is how I calculated $130.79 by hand.
Base Rate up to 3000 gallons = $ 11.52
Next 10,000 gallons * .00087 = $ 8.70
Remaining 153,570 gallons * .00072 $ 110.57

Total gallons used 166,570 = $130.79
Hope this is more understanding.

 

[Bernard Liengme]

Yes, you could use IF but it just gets in the way

Here is my worksheet
gallons base upto 10K over 10K Total
166570 11.52 8.7 110.5704 130.79
2999 11.52 0 0 11.52
5000 11.52 1.74 0 13.26
12000 11.52 7.83 0 19.35
15500 11.52 8.70 1.80 22.02

In C2: =(A2>3000)*MIN(A2-3000,10000)*0.00087
In D2: =(A2>13000)*(A2-13000)*0.00072
In E2: =ROUND(SUM(B22),2)

Note how Excel will evaluate (A2>13000) as TRUE or FALSE but then we
multiply this by a number so Excel treats TRUE as 1 and FALSE as 0
The MIN bit means we use up to 10,000 galls times the 87 factor

If you experiment with this and agree with the results, you can combine into
one formula
=11.52+ROUND(((A2>3000)*MIN(A2-3000,10000)*0.00087+(A2>13000)*(A2-13000)*0.00072),2)

best wishes

 

[Deano]

Bernard,
Thank you it works great. I would have never thought to use the MIN formula
in that situation.

 

[Bernard Liengme]

Thanks for the feedback
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Bernard,
Thank you it works great. I would have never thought to use the MIN
formula
in that situation.