How can I make my IF/OR formula work?

[kellyfirth]

I'm not sure why this formula isn't working..it's probably just a simple mistake; I've been tinkering with it for half an hour now and I don't understand what's wrong.

I'm trying to write a formula which will check if the value in cell B1 or D1 is #N/A or 0 and if so, write 'DELETE.'

Example of how it should look if working:

A B C D E
Name1 21 USD 04/12/2015
Name2 #N/A EUR #N/A DELETE
Name3 1.29 USD 24/05/2015
Name4 0 GBP 01/01/2014 DELETE
Name5 1009 CHF 0 DELETE

The formula I'm currently using is:
=IF(OR(B1="N/A",D1="#N/A",B1=0,D1=0),"DELETE","")

This works if the cell contains a 0, it will indeed populate with: DELETE. But the #N/A part is just returning #N/A rather than: DELETE. Does anyone know how to fix this? I've tried using the ISNA formula but I can't get this to work either.

Thanks in advance!

[JohnTopley]

Is #N/A the result of a formula? If so get rid of the #N/A

use

=IFERROR(formula,0)

You can just check for zero

[Pete_UK]

You need to check for the errors first. Try it like this:

=IF(OR(ISNA(B1),ISNA(D1)),"DELETE",IF(OR(B1=0,D1=0),"DELETE",""))

Hope this helps.

Pete

[cbatrody]

Try the following:

=IF(OR(ISNA(B1),ISNA(D1),B1=0,D1=0),"DELETE","")

[kellyfirth]

Many thanks for suggestions! I tested Pete's formula and this seems to work. I'd tried something similar but hadn't check for the errors first using ISNA and then the 0's. Thank you!