IF Statement
 

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

IF Statement
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"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

IF Statement
 

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

IF Statement
 

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

IF Statement
 

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

IF Statement
 

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

IF Statement
 

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