Display "0" instead of #N/A
sunray2000
Posted messages
10
Status
Membre
-
Nono -
Nono -
Hello,
Actually, I'm using the VLOOKUP function
and depending on the criteria I've written, it either gives me the result I want it to display, or it returns #N/A. (It's normal for it to give that).
However, I want it to display 0 (zero) instead of #N/A. (Because I'm using the SUM function afterwards and as a result, it doesn't work)
How can I do that?
Thank you
Best regards.
Actually, I'm using the VLOOKUP function
and depending on the criteria I've written, it either gives me the result I want it to display, or it returns #N/A. (It's normal for it to give that).
However, I want it to display 0 (zero) instead of #N/A. (Because I'm using the SUM function afterwards and as a result, it doesn't work)
How can I do that?
Thank you
Best regards.
4 answers
Hi,
there is the isna function that tests if a cell contains #N/A..
=ISNA(A2)
It is possible to combine it with a test to display 0 if it is indeed #N/A.
=IF(ISNA(A1),"0",A1)
There are a series of functions like that to test special cases:
ISERR the value refers to one of the error values, except for #N/A.
ISERROR the value refers to one of the error values (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL!).
ISLOGICAL the value refers to a logical value.
ISNA the value refers to the error value #N/A (not available).
ISNONTEXT the value refers to anything that is not text. (This function returns TRUE if the argument value refers to an empty cell.)
ISNUMBER the value refers to a number.
ISREF the value refers to a reference.
ISTEXT the value refers to text.
ISBLANK the value refers to an empty cell.
Check the help for the IS... functions
--
A+ Blux
there is the isna function that tests if a cell contains #N/A..
=ISNA(A2)
It is possible to combine it with a test to display 0 if it is indeed #N/A.
=IF(ISNA(A1),"0",A1)
There are a series of functions like that to test special cases:
ISERR the value refers to one of the error values, except for #N/A.
ISERROR the value refers to one of the error values (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL!).
ISLOGICAL the value refers to a logical value.
ISNA the value refers to the error value #N/A (not available).
ISNONTEXT the value refers to anything that is not text. (This function returns TRUE if the argument value refers to an empty cell.)
ISNUMBER the value refers to a number.
ISREF the value refers to a reference.
ISTEXT the value refers to text.
ISBLANK the value refers to an empty cell.
Check the help for the IS... functions
--
A+ Blux
"Les cons, ça ose tout. C'est même à ça qu'on les reconnait"
Nono
Thank you, I wasn't aware of these functions that are very useful.
