Combining ISNA and IF formulas

[ottodesque]

I am trying to write a formula that returns a value of blank if the contents
of field All Audit Compilation 1'!K3584 is either NA or P and the value of 1
if it is not NA or P. I started with =IF(ISNA('All Audit Compilation
1'!K3584),"") but still get the value of false if the field is blank and true
if it contains NA. I have the formula IF('All Audit Compilation
1'!K3585="P",â€â€,"1") which works fine on its own but do not know how to
combine the two formulas and have =IF(ISNA('All Audit Compilation
1'!K3584),"") return a value other than true or false

 

[Shane Devenshire]

Hi,

Try something like

=IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation
1'!K3584)),"",'All Audit Compilation 1'!K3584)

 

[ottodesque]

Returns value NA

Hi,

Try something like

=IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation
1'!K3584)),"",'All Audit Compilation 1'!K3584)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[T. Valko]

That won't work. When the cell contains an error then the test for K3584="P"
returns the error and blows up the OR condition.

You have to first test for the error:

=IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation
1'!K3584="P",'All Audit Compilation 1'!K3584,""))

 

[ottodesque]

I think we are getting closer. Here is what I have found.

=IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation
1'!K3584="P","",1)) STILL COUNTS NA AS 1

=IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation
1'!K3584="P",'All Audit Compilation 1'!K3584,"")) COUNTS P AS P AND DOES NOT
RETURN THE VALUE OF 1 FOR ALL CELL CONTENTS OTHER THAN P OR NA

 

[T. Valko]

=IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation
1'!K3584="P","",1)) STILL COUNTS NA AS 1

Ok, we need to clarify something.

Are you wanting to test for the specific *ERROR* #N/A or the *TEXT* value
NA?

#N/A and NA are not the same. The ISNA function tests for the specific #N/A
error.

See if this is what you want. Testing for the *TEXT* value "NA" then you can
use OR:

=IF(OR('All Audit Compilation 1'!K3584={"NA","P"},"",1)

Note that an empty cell will return 1. Is that what you want? If not:

=IF(OR('All Audit Compilation 1'!K3584={"","NA","P"},"",1)