View Single Post
  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

If you have this VLOOKUP formula, say, in 1000 or more cells, it might
be better to restort to formulas like:

=SUMIF(Range,"<#N/A") for summing;

{=AVERAGE(IF(ISNUMBER(Range),Range,""))} for averaging;

and comparable formulas for other statistics than trying to suppress
#N/A's by formulas that compute the same thing twice like:

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

dave wrote:
When I try to look with vlookup for a value that is not in the table
indicated, I get the message #N/A which indicates that the value cannot be
found (this is normal).

I would like to change this message #N/A into a value 0 or a blanc ""
because you cannot calculate with this value.

Is this possible??


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.