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.
|