Prev: unlock frozen cells
Next: How do I create a drop box in Excel to show a calendar?
From: Chris on 29 Apr 2010 13:46
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
From: Eduardo on 29 Apr 2010 13:59
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
From: Joe User on 29 Apr 2010 14:00
"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
From: Gary Brown on 29 Apr 2010 14:00
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
From: Mike H on 29 Apr 2010 14:04
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
 |  Next  |  Last
Pages: 1 2
Prev: unlock frozen cells
Next: How do I create a drop box in Excel to show a calendar?