IF Statement

  • Thread starter Thread starter Deano
  • Start date Start date
D

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
Click to expand...
<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.
 
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
 
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
 
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.
 
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(B2:D2),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
 
Bernard,
Thank you it works great. I would have never thought to use the MIN formula
in that situation.
 
Back
Top