0 instead of #N/A

Posted by Ben on April 12, 2001 10:49 AM

How do I make a cell display 0 instead of #N/A?
Thanks

Posted by Dave Hawley on April 12, 2001 10:54 AM

Hi Ben

Try this:

=IF(ISNA(SUM(A10:A12)),0,SUM(A10:A12))

Dave

OzGrid Business Applications

Posted by Aladin Akyurek on April 13, 2001 4:09 AM

The #N/A problem

Ben

If you get the #N/A as a result of a VLOOKUP formula, you might use the MATCH+VLOOKUP combination that has just been produced at this bord (I owe this Malcolm Robertson) instead of much used IF(ISNA(VLOOKUP(...)),0,VLOOKUP(...)).

See: 14398.html

Note. I'm not sure whether ISNUMBER+MATCH+VLOOKUP is cheaper than carrying out ISNA+VLOOKUP+VLOOKUP.

You can also get #N/A's with array-formulas, but that requires of course a different treatment. See Excel's #N/A entry under Contents And Index via Help for other possibilities.

Hope this helps.

Aladin