Not showing #NA on spreadsheet

[jaypatel]

Is there a way of showing and calculating #NA on a spreadsheet..... maybe the easiest would be to delete them entirely......

My #NA errors are via vlookup's and the #NA does not affect my calculations other than that the sum total is also #NA

Jay

 

[Aladin Akyurek]

Is there a way of showing and calculating #NA on a spreadsheet..... maybe the easiest would be to delete them entirely......

My #NA errors are via vlookup's and the #NA does not affect my calculations other than that the sum total is also #NA

Jay

Either change your VLOOKUP formula to return 0 or even "" or use:

=SUMIF(Range,"<>#N/A")

where Range houses #N/A's.

 

[jaypatel]

how would you change the vlookup formula to return the value to zero if it is #N/A.

Jay

 

[Todd Bardoni]

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

 

[Aladin Akyurek]

how would you change the vlookup formula to return the value to zero if it is #N/A.

Jay

By picking out the most efficient method from my contrib in:

http://www.mrexcel.com/board2/viewtopic.php?t=62102