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


"Deano" wrote in message
...
Bernard,
Thank you it works great. I would have never thought to use the MIN
formula
in that situation.
--
Thanks for your time


"Bernard Liengme" wrote:

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: =(A23000)*MIN(A2-3000,10000)*0.00087
In D2: =(A213000)*(A2-13000)*0.00072
In E2: =ROUND(SUM(B2:D2),2)

Note how Excel will evaluate (A213000) 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(((A23000)*MIN(A2-3000,10000)*0.00087+(A213000)*(A2-13000)*0.00072),2)

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"Deano" wrote in message
...
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.
--
Thanks for your time


"John" wrote:

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(A113000,A1*0.00072+11.52,IF(A13000,A1*0.0008 7+11.52,IF(A1<=3000,11.52)))
I would create a small table and use cell reference instead of
numbers.
e.g: =IF(A1C6,A1*D7+B7,IF(A1B6,A1*C7+B7,IF(A1<=B6,B7) ))
HTH
John
"Deano" wrote in message
...
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.
--
Thanks for your time