Need Help with #N/A in equation
 

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

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

Regards

Trevor


"mkerstei" wrote in
message ...

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

mkerstei 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?

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.


--
BenjieLop


------------------------------------------------------------------------
BenjieLop's Profile: http://www.excelforum.com/member.php...o&userid=11019
View this thread: http://www.excelforum.com/showthread...hreadid=391362

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 ,
mkerstei
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?

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=391362

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


--
mkerstei
------------------------------------------------------------------------
mkerstei's Profile: http://www.excelforum.com/member.php...o&userid=25688
View this thread: http://www.excelforum.com/showthread...hreadid=391362

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

Cheers!

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=391362

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 ,
swatsp0p 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)