Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

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

Check out our Excel Resources

Re: 0 instead of #N/A

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


The #N/A problem

Posted by Aladin Akyurek on April 13, 2001 4:09 AM
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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.