Need Help with #N/A in equation

[mkerstei]

I have a set equation that needs to be run on about a thousand different entries. A simplified version of the equation would be =A+B+C-D. The only problem is, "B" is only found in some of the entries. So for the majority of the times the equation is run for the different entries, it works, but when "B" is nonexistent, the equation returns a #N/A value. Is there anyway I can keep the set equation but make it work when B does not exist?

[BenjieLop]

I have a set equation that needs to be run on about a thousand different entries. A simplified version of the equation would be =A+B+C-D. The only problem is, "B" is only found in some of the entries. So for the majority of the times the equation is run for the different entries, it works, but when "B" is nonexistent, the equation returns a #N/A value. Is there anyway I can keep the set equation but make it work when B does not exist?

Will this work for you?

=if(B="",A+C+D,A+B+C+D)

or

=if(iserror(A+B+C+D),A+C+D,A+B+C+D)

Regards.

[swatsp0p]

Great solutions, Benjie. May I suggest the OP use the ISNA function instead of ISERROR? ISNA will only ignore the error caused by the #N/A, where ISERROR will ignore ALL errors, regardless of the cause and may hide problems elsewhere in the data.

=if(isna(A+B+C+D),A+C+D,A+B+C+D)

I have seen both Ron deBruin and Dave Peterson make this observation.

Cheers!

Bruce

[Trevor Shuttleworth]

I think we'll need to see the actual equation as the simplified version will
still work with B blank

Regards

Trevor


"mkerstei" <[email protected]> wrote in
message news:[email protected]...
>
> I have a set equation that needs to be run on about a thousand different
> entries. A simplified version of the equation would be =A+B+C-D. The
> only problem is, "B" is only found in some of the entries. So for the
> majority of the times the equation is run for the different entries, it
> works, but when "B" is nonexistent, the equation returns a #N/A value.
> Is there anyway I can keep the set equation but make it work when B
> does not exist?
>
>
> --
> mkerstei
> ------------------------------------------------------------------------
> mkerstei's Profile:
> http://www.excelforum.com/member.php...o&userid=25688
> View this thread: http://www.excelforum.com/showthread...hreadid=391362
>

[JE McGimpsey]

If B is really "nonexistent" then you wouldn't get #N/A.

AFAIK, the only way the + operator returns #N/A is if one of the
arguments has a value of #N/A.

So what's really going on?

If you're using a lookup, you can substitute something like:

=IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...))

If you don't want a zero to appear, either custom format it:

Format/Cells/Number/Custom General;General;;@

or, if zero is a valid value, use


=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

and change your summary formula to

=SUM(A1:C1,-D1)

since SUM() ignores text, but the + operator throws a #VALUE! error.



In article <[email protected]>,
mkerstei <[email protected]>
wrote:

> I have a set equation that needs to be run on about a thousand different
> entries. A simplified version of the equation would be =A+B+C-D. The
> only problem is, "B" is only found in some of the entries. So for the
> majority of the times the equation is run for the different entries, it
> works, but when "B" is nonexistent, the equation returns a #N/A value.
> Is there anyway I can keep the set equation but make it work when B
> does not exist?

[mkerstei]

Thank you both for your input. You have solved my problem and saved me hours of frustration.

[swatsp0p]

Benjielop did the work... wtg. Thanks for the feedback, it is always appreciated.

Cheers!

Bruce

[JE McGimpsey]

A bit more efficient:

If the only cell that may contain the #N/A is B, then there's no need to
resolve the references and do the addition when checking:

=IF(ISNA(B),0,B)+A+C-D

If the #N/A could occur in other columns, then the proposed solution
fails.

Note that the OP said that D should be subtracted, not added.

In article <[email protected]>,
swatsp0p <[email protected]> wrote:

> Great solutions, Benjie. May I suggest the OP use the ISNA function
> instead of ISERROR? ISNA will only ignore the error caused by the
> #N/A, where ISERROR will ignore ALL errors, regardless of the cause and
> may hide problems elsewhere in the data.
>
> =if(isna(A+B+C+D),A+C+D,A+B+C+D)