#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
davesexcel
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
andy62
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
andy62
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
John
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
2 Nesting questions Starchaser Excel Worksheet Functions 7 January 20th 06 07:53 PM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM
Match / Vlookup within an Array formula Hari Prasadh Excel Discussion (Misc queries) 3 February 3rd 05 05:37 PM


All times are GMT +1. The time now is 06:55 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"