N/A

[freddo]

I am using a look up function and N/A appears whichs means it throws the auto sum out, how do I keep the cell blank instead of showing N/A

[Sisyphus]

Hi,

Use the ISNA function:

Code: Select all

= if(ISNA( [Place your existing function here]), "",  [Repeat your existing function])


The True part of the IF function will write "" to the cell instread of the N/A message. If you need to use the result of the cell in further calculations you may have to write 0 (zero) instead.

I hope this helps. Have a pleasant evening!

[Alkhader]

Dear Sisyphus

Can you attach Excel attach with formula ISNA to hide "N/A".
Thanks

[Sisyphus]

Here is the demonstration:

Code: Select all

=IF(ISNA(MATCH(B$3:B$6,5)),"Error was trapped",MATCH(B$3:B$6,5))


The range B3:B6 was filled with the number 1 to 4.
The MATCH function can't find the number 5 among them, and reports "#N/A"
The ISNA function traps the error and returns a result other than that of the MATCH function.

The attached worksheet has all that in 3D
Have a great day!

[Alkhader]

Thanks a lot friend. I would like you to help me in how do I keep the cell blank instead of showing N/A when there is a formula in the cell.

Sorry for being fussy in details "in my questions".

Alkhader

[Sisyphus]

In the formula posted above the words "Error was trapped" will be written in place of the result. You can change this to anything you like. Since your needs are simple, the answer is simple, too. Just write "" - nothing, blank, Null, zilch. The formula would look like this now:

Code: Select all

=IF(ISNA(MATCH(B$3:B$6,5)),"",MATCH(B$3:B$6,5))


Just so that we don't loose you on the way, the IF function has three parts separated by commas
IF( [Condition], [Do if condition is True], [Do if Condition is False] )
In the above formula this scheme plays out like this:
=IF( Condition=[MATCH produces NA], True = "", False =MATCH )
Don't let the inverted True and False confuse you. In plain language: If it is True that MATCH produces NA, then insert nothing, Otherwise continue to insert the MATCH result.

Have a great day!