How do I combine ISNA and IF functions

[ottodesque]

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

 

[Jacob Skaria]

Try

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

 

[Teethless mama]

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

your formula returns #N/A if #N/A in a cell


Try it like this:

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

 

[Jacob Skaria]

Oops..you are right.

 

[ottodesque]

When I try this formula it tells me that a parenthesis is missing but I
cannot figure out where.

 

[Jacob Skaria]

Basically it is ...You can change the cell reference to suit

=IF(ISNA(A1),"",IF(A1="P","",1))

If this post helps click Yes

 

[ottodesque]

I have tried this but if the cell is NA it shows a value of 1

 

[Shane Devenshire]

Hi,

Please don't post twice.

 

[karenk]

Very helpful tips with the ISNA function. The only trouble is I want a blank
return, not a zero return because I am calculating an average over a five
work days period and if I have only three days data, I would like my formula
to calculate the average over the three days. I am using a combination of IF,
VLOOKUP, WORKDAY, and AVERAGE functions.

Appreciate if very much if anyone could help.