Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula
I need a formula that has many functions. The formula will be in cell g2. I
want to check cell e2, and if it =0 then cell g2 should show a 0. If e2 is less than 50,000 then the number in a1 should show in g2. If the number in e2 is greater than 50,000 then I need a formula that takes the number in e2 subtracts the number in d1, then divides that number by 1,000. Then that number is multiplied the number in c1 and then add to this number the number in a1. Basically the number in a1 is 166.35 and does not change. The number in c1 is 2.81 and does not change. The number in d1 is 50,000 and does not change. The number in e2 is the only one that changes and this is the usage number. The customer receives the 50,000 gallons for 166.35 and for every 1,000 gallons over 50,000 they must pay 2.81. However if the number in e2 is blank or 0 then I need the number in g2 to be 0. If this is too confusing please let me know and I will try to simplify it. Thanks for any help. -- John |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula
well yeh, simplify it, this looks like something that has to be done in VBA -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=542730 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula
try pasting this in cell G2:
=IF(E2=0,0,IF(E2<50000,$A$1,$A$1+$C$1*(E2-$D$1)/1000)) The $ characters will preserve the references to the fixed cells (A1, C1, and D1) if you copy the formula down to other rows below G2. Hope that helps. "John" wrote: I need a formula that has many functions. The formula will be in cell g2. I want to check cell e2, and if it =0 then cell g2 should show a 0. If e2 is less than 50,000 then the number in a1 should show in g2. If the number in e2 is greater than 50,000 then I need a formula that takes the number in e2 subtracts the number in d1, then divides that number by 1,000. Then that number is multiplied the number in c1 and then add to this number the number in a1. Basically the number in a1 is 166.35 and does not change. The number in c1 is 2.81 and does not change. The number in d1 is 50,000 and does not change. The number in e2 is the only one that changes and this is the usage number. The customer receives the 50,000 gallons for 166.35 and for every 1,000 gallons over 50,000 they must pay 2.81. However if the number in e2 is blank or 0 then I need the number in g2 to be 0. If this is too confusing please let me know and I will try to simplify it. Thanks for any help. -- John |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula
try pasting this in cell G2:
=IF(E2=0,0,IF(E2<50000,$A$1,$A$1+$C$1*(E2-$D$1)/1000)) The $ characters will preserve the references to the fixed cells (A1, C1, and D1) if you copy the formula down to other rows below G2. Hope that helps. "John" wrote: I need a formula that has many functions. The formula will be in cell g2. I want to check cell e2, and if it =0 then cell g2 should show a 0. If e2 is less than 50,000 then the number in a1 should show in g2. If the number in e2 is greater than 50,000 then I need a formula that takes the number in e2 subtracts the number in d1, then divides that number by 1,000. Then that number is multiplied the number in c1 and then add to this number the number in a1. Basically the number in a1 is 166.35 and does not change. The number in c1 is 2.81 and does not change. The number in d1 is 50,000 and does not change. The number in e2 is the only one that changes and this is the usage number. The customer receives the 50,000 gallons for 166.35 and for every 1,000 gallons over 50,000 they must pay 2.81. However if the number in e2 is blank or 0 then I need the number in g2 to be 0. If this is too confusing please let me know and I will try to simplify it. Thanks for any help. -- John |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
If formula
The formula worked perfectly! Thanks for your help.
-- John "andy62" wrote: try pasting this in cell G2: =IF(E2=0,0,IF(E2<50000,$A$1,$A$1+$C$1*(E2-$D$1)/1000)) The $ characters will preserve the references to the fixed cells (A1, C1, and D1) if you copy the formula down to other rows below G2. Hope that helps. "John" wrote: I need a formula that has many functions. The formula will be in cell g2. I want to check cell e2, and if it =0 then cell g2 should show a 0. If e2 is less than 50,000 then the number in a1 should show in g2. If the number in e2 is greater than 50,000 then I need a formula that takes the number in e2 subtracts the number in d1, then divides that number by 1,000. Then that number is multiplied the number in c1 and then add to this number the number in a1. Basically the number in a1 is 166.35 and does not change. The number in c1 is 2.81 and does not change. The number in d1 is 50,000 and does not change. The number in e2 is the only one that changes and this is the usage number. The customer receives the 50,000 gallons for 166.35 and for every 1,000 gallons over 50,000 they must pay 2.81. However if the number in e2 is blank or 0 then I need the number in g2 to be 0. If this is too confusing please let me know and I will try to simplify it. Thanks for any help. -- John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Nesting questions | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |