How to treat "#N/A"s in IF functions

I've got two columns of data. I'm trying to test if either cell has a value greater than 0 to return a output. Say, in this example "has dividend". My problem is with #NA cells. Is there a way to ignore null values?

I've also got some cells in the data that contain "#VALUE!". What is the basic difference between #VALUE and #NA?

Thanks for the help!

VALUE errors usually occur from incorrect argument type, ie. using a string where numbers are required.

N/A errors mean literally "No Value available"


you can use the function ISERR() to check if a cell contains an error message.

for example:
=IF(iserr(A1),"",A1)

Sorry Geoff, just reread your post.

This would be more like what you are after:

=IF(ISERR(A1),"",IF(A1=0,"","had dividend"))

Sorry, another ammendment:

The ISERR function will work for all errors EXCEPT N/A.

You will need to replace ISERR with ISERROR, which should work

I always understood #VALUE! to be "doesn't make sense" and #NA to mean "can not find".
You'll get a value for instance, if you try to multiply "1" and "two". Excel doesn't recognize "two" as something that can be multiplied.
#NA means that Excel couldn't find the answer. I honestly don't understand how an if statement could come back with an NA...

You can use ISERROR along with an if to get rid of any "#" results.

An example would be a great help to understand where you are having your problem.


--- sorry folks... was typing this and didn't realize other people were responding at the same time!!

Sorry sorry, should have given a clearer example.

A1 has say "0", B1 has "#Value (or #NA)", I want C1 to output "no dividend". If either A1 or B1 contain a value greater than 0, regardless of the other cell, then i want the output to be "dividend".

I'm tinkering.

I'm assuming that you won't have negative values in either cell, in which case

=IF(SUMIF(A1:B1,">0"),"dividend","no dividend")

=IF(SUMIF(A1:B1,">0"),"dividend","no dividend")

has errors when there is a "#N/A" in A1 or B1. It outputs "no dividend" when there is an amount over 0 in A1 or B1.

Perhpas test for errors in each cell with a nest IF?

=IF(AND(SUMIF(A1:B1,">0"),NOT(ISERROR(A1)),NOT(ISERROR(B1))),"dividend",IF(AND(SUMIF(A1:B1,"<=0"),NOT(ISERROR(A1)),NOT(ISERROR(B1))),"no dividend",""))

Geoff, how about this:

=IF(OR(ISERROR(B1),ISERROR(A1)),"No Dividend",IF(OR(B1=0,A1=0),"No Dividend","Dividend"))

Originally Posted by Geoff1184

=IF(SUMIF(A1:B1,">0"),"dividend","no dividend")

has errors when there is a "#N/A" in A1 or B1. It outputs "no dividend" when there is an amount over 0 in A1 or B1.

That can only happen if your "amount" is text formatted. What generates the values in A1 and B1? If these are from formulas then you could amend the formulas to ensure that values are numeric and then the above would work. Even better would be to amend the formulas so that error values are also suppressed......

If you can't change the data try like this:

=IF(IF(ISNUMBER(A1+0),IF(A1+0>0,1))+IF(ISNUMBER(B1+0),IF(B1+0>0,1)),"dividend","no dividend")

or an "array formula"

=IF(SUM(IF(ISNUMBER(A1:B1+0),IF(A1:B1+0>0,1))),"dividend","no dividend")

which needs to be confirmed with CTRL+SHIFT+ENTER