#N/A N.A. Error

[brettmanning24]

Hello,

I have a spreadsheet which returns live market prices. From time to
time I receive an error as the price is no available, the error I
receive is #N/A N.A..

I would like to display a warning message to the users that a price is
in error, I would like to check a range of cells for the #N/A N.A.
error, I have over 100 prices.

I hope someone can let me know the best way to do this?

I have tried ISERR and ISNA but it does not work and returns the
result "FALSE".

Thanks in advance,

Brett

 

[Fred Smith]

It may be an error to you, but it's not to Excel. It's just text to Excel.
Using:

=if(a1="#N/A N.A.","Price Not Available",a1)

should work. Copy down for all your prices.

 

[Guest]

Hi Brette,

use function ISERROR().

 

[brettmanning24]

It may be an error to you, but it's not to Excel. It's just text to Excel.
Using:

=if(a1="#N/A N.A.","Price Not Available",a1)

should work. Copy down for all your prices.

--
Regards,
Fred












- Show quoted text -

Thank you for your reply.

Do you know if it is possible to use an IF Statement on a range. For
example if A1:A100 contains "Error" display Error, if no error is
found display "OK".

Thanks,

Brett

 

[Fred Smith]

One way would be:

=if(countif(a1:a100,"Error")>0,"Error","OK")