Hiding #N/A (E2000)

Topic

New Reply

Happy New Year to all

I have a cell that until other information is entered in other cells shows #N/A, when the other cells are completed a value is calculated and then shows in this cell, it all works fine but I would like to hide the #N/A. The Excel help says that a green triangle will show in the corner of the cell if there is an error in the formula and by formating it it can be hidden, however I have no green triangle because there is not a problem with the actual formula.

Any advice please

Cheers

Stephen

Reply | Quote

Replies

The general idea is to use IF and ISNA to test if the formula results in #N/A. You can replace

=MYFORMULA

with

=IF(ISNA(MYFORMULA),"",MYFORMULA)

Here is a specific example: your cell contains the formula

=VLOOKUP(A1,B1:C10,2,FALSE)

To avoid the #N/A error value if A1 is not found in B1:B10, change this to

=IF(ISNA(VLOOKUP(A1,B1:C10,2,FALSE)),"",VLOOKUP(A1,B1:C10,2,FALSE))

You can also use the more general ISERROR instead of ISNA.

Reply | Quote

The general idea is to use IF and ISNA to test if the formula results in #N/A. You can replace

=MYFORMULA

with

=IF(ISNA(MYFORMULA),"",MYFORMULA)

Here is a specific example: your cell contains the formula

=VLOOKUP(A1,B1:C10,2,FALSE)

To avoid the #N/A error value if A1 is not found in B1:B10, change this to

=IF(ISNA(VLOOKUP(A1,B1:C10,2,FALSE)),"",VLOOKUP(A1,B1:C10,2,FALSE))

You can also use the more general ISERROR instead of ISNA.

Reply | Quote

You can use conditional formatting to set the text color to match the background color
If the cell is A1 you could use a formula is:

=isna(a1)

[Modify as needed]

Then set the format of the text color to match the background

You could check the other information is available (if you are wainting for cells A1-C1), something like:

=if(or(a1="",B1="",C1=""),"",[your current formula])

Steve

Reply | Quote

You can use conditional formatting to set the text color to match the background color
If the cell is A1 you could use a formula is:

=isna(a1)

[Modify as needed]

Then set the format of the text color to match the background

You could check the other information is available (if you are wainting for cells A1-C1), something like:

=if(or(a1="",B1="",C1=""),"",[your current formula])

Steve

Reply | Quote

Thanks Hans and Steve for the prompt response

Stephen

Reply | Quote