| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Embedding an OR statement in an IF statement efficiently | Excel Discussion (Misc queries) | |||
| If statement or lookup statement not sure | Excel Worksheet Functions | |||
| SUMIF statement with AND statement | Excel Discussion (Misc queries) | |||
| Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
| appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
Linear Mode
