Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Div / 0
Hello all
How can I get this formula to not return Div/0. N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9 It works ok so long as one of either P39, P41, P43, P45 has a value greater than 0. If all are "0" then it returns Div/0. if all are "0" the I need N32 to = N34-N30 I hope you can follow this so you can help. ??????? Thanks Chris |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Div / 0
Hi,
=if(iserror(N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9),N34-N30,N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9) "Chris" wrote: Hello all How can I get this formula to not return Div/0. N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9 It works ok so long as one of either P39, P41, P43, P45 has a value greater than 0. If all are "0" then it returns Div/0. if all are "0" the I need N32 to = N34-N30 I hope you can follow this so you can help. ??????? Thanks Chris |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Div / 0
"Chris" wrote:
N32=N34 -Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30) +P45/16.9 [....] If all are "0" then it returns Div/0. if all are "0" the I need N32 to = N34-N30 I suspect you want: =N34 - (if(E52=0,0,P39/E52) + if(C32=0,0,P41/C32) + P43/16.9 + N30) + P45/16.9 Note the absence of the use of SUM. It was superfluous in your case. Also note that the formula can be made a little more readable by applying some basic math to simplify it, to wit: =N34 - if(E52=0,0,P39/E52) - if(C32=0,0,P41/C32) - P43/16.9 - N30 + P45/16.9 You can one step further: =N34 - if(E52=0,0,P39/E52) - if(C32=0,0,P41/C32) - N30 + (P45-P43)/16.9 ----- original message ----- "Chris" wrote: Hello all How can I get this formula to not return Div/0. N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9 It works ok so long as one of either P39, P41, P43, P45 has a value greater than 0. If all are "0" then it returns Div/0. if all are "0" the I need N32 to = N34-N30 I hope you can follow this so you can help. ??????? Thanks Chris |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Div / 0
Actually it's C32 and E52 who are that dasterdly deed doers!
This should work in N32... =IF(OR(E52=0,C32=0),N34-N30,N34-SUM((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9) -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Chris" wrote: Hello all How can I get this formula to not return Div/0. N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9 It works ok so long as one of either P39, P41, P43, P45 has a value greater than 0. If all are "0" then it returns Div/0. if all are "0" the I need N32 to = N34-N30 I hope you can follow this so you can help. ??????? Thanks Chris |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Div / 0
Chris,
It's C32 & E32 that must be populated for the formula to work, try this. =IF(COUNT(C32,E52)=2,N34-SUM((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9,"") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Chris" wrote: Hello all How can I get this formula to not return Div/0. N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9 It works ok so long as one of either P39, P41, P43, P45 has a value greater than 0. If all are "0" then it returns Div/0. if all are "0" the I need N32 to = N34-N30 I hope you can follow this so you can help. ??????? Thanks Chris |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Div / 0
And to make it easier to follow, get rid of the unneeded Sum function and
parentheses: =IF(COUNT(C32,E52)=2,N34-P39/E52+P41/C32+P43/16.9+N30+P45/16.9,"") Fred "Mike H" wrote in message ... Chris, It's C32 & E32 that must be populated for the formula to work, try this. =IF(COUNT(C32,E52)=2,N34-SUM((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9,"") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Chris" wrote: Hello all How can I get this formula to not return Div/0. N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9 It works ok so long as one of either P39, P41, P43, P45 has a value greater than 0. If all are "0" then it returns Div/0. if all are "0" the I need N32 to = N34-N30 I hope you can follow this so you can help. ??????? Thanks Chris |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Div / 0
I think you've got some signs reversed compared with what the OP was asking
for, Fred. Try =IF(COUNT(C32,E52)=2,N34-P39/E52-P41/C32-P43/16.9-N30+P45/16.9,"") -- David Biddulph "Fred Smith" wrote in message ... And to make it easier to follow, get rid of the unneeded Sum function and parentheses: =IF(COUNT(C32,E52)=2,N34-P39/E52+P41/C32+P43/16.9+N30+P45/16.9,"") Fred "Mike H" wrote in message ... Chris, It's C32 & E32 that must be populated for the formula to work, try this. =IF(COUNT(C32,E52)=2,N34-SUM((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9,"") -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "Chris" wrote: Hello all How can I get this formula to not return Div/0. N32=N34-Sum((P39/E52)+(P41/C32)+(P43/16.9)+N30)+P45/16.9 It works ok so long as one of either P39, P41, P43, P45 has a value greater than 0. If all are "0" then it returns Div/0. if all are "0" the I need N32 to = N34-N30 I hope you can follow this so you can help. ??????? Thanks Chris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|