
Next: when field contains 
Author 
Message 
ottodesque External
Since: Apr 24, 2009 Posts: 4

Posted: Thu Jul 09, 2009 12:01 pm Post subject: How do I combine ISNA and IF functions Archived from groups: microsoft>public>excel>worksheet>functions (more info?) 


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 

Back to top 


Jacob Skaria External
Since: Mar 04, 2009 Posts: 568

Posted: Thu Jul 09, 2009 12:17 pm Post subject: RE: How do I combine ISNA and IF functions [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


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

If this post helps click Yes

Jacob Skaria
"ottodesque" wrote:
> 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 

Back to top 


Teethless mama External
Since: Sep 17, 2006 Posts: 1529

Posted: Thu Jul 09, 2009 12:27 pm Post subject: RE: How do I combine ISNA and IF functions [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


> =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" wrote:
> Try
>
> =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation
> 1'!K3584),"",1)
>
> 
> If this post helps click Yes
> 
> Jacob Skaria
>
>
> "ottodesque" wrote:
>
> > 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 

Back to top 


Jacob Skaria External
Since: Mar 04, 2009 Posts: 568

Posted: Thu Jul 09, 2009 12:42 pm Post subject: RE: How do I combine ISNA and IF functions [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


Oops..you are right.

If this post helps click Yes

Jacob Skaria
"Teethless mama" wrote:
> > =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" wrote:
>
> > Try
> >
> > =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation
> > 1'!K3584),"",1)
> >
> > 
> > If this post helps click Yes
> > 
> > Jacob Skaria
> >
> >
> > "ottodesque" wrote:
> >
> > > 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 

Back to top 


ottodesque External
Since: Apr 24, 2009 Posts: 4

Posted: Thu Jul 09, 2009 12:58 pm Post subject: RE: How do I combine ISNA and IF functions [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


When I try this formula it tells me that a parenthesis is missing but I
cannot figure out where.
"Jacob Skaria" wrote:
> Try
>
> =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation
> 1'!K3584),"",1)
>
> 
> If this post helps click Yes
> 
> Jacob Skaria
>
>
> "ottodesque" wrote:
>
> > 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 

Back to top 


Jacob Skaria External
Since: Mar 04, 2009 Posts: 568

Posted: Thu Jul 09, 2009 1:09 pm Post subject: RE: How do I combine ISNA and IF functions [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


Basically it is ...You can change the cell reference to suit
=IF(ISNA(A1),"",IF(A1="P","",1))
If this post helps click Yes

Jacob Skaria
"ottodesque" wrote:
> When I try this formula it tells me that a parenthesis is missing but I
> cannot figure out where.
>
> "Jacob Skaria" wrote:
>
> > Try
> >
> > =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation
> > 1'!K3584),"",1)
> >
> > 
> > If this post helps click Yes
> > 
> > Jacob Skaria
> >
> >
> > "ottodesque" wrote:
> >
> > > 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 

Back to top 


ottodesque External
Since: Apr 24, 2009 Posts: 4

Posted: Thu Jul 09, 2009 1:16 pm Post subject: RE: How do I combine ISNA and IF functions [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


I have tried this but if the cell is NA it shows a value of 1
"Teethless mama" wrote:
> > =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" wrote:
>
> > Try
> >
> > =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation
> > 1'!K3584),"",1)
> >
> > 
> > If this post helps click Yes
> > 
> > Jacob Skaria
> >
> >
> > "ottodesque" wrote:
> >
> > > 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 

Back to top 


Shane Devenshire External
Since: Jan 07, 2009 Posts: 257

Posted: Thu Jul 09, 2009 1:30 pm Post subject: RE: How do I combine ISNA and IF functions [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


Hi,
Please don't post twice.

If this helps, please click the Yes button.
Cheers,
Shane Devenshire
"ottodesque" wrote:
> 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 

Back to top 


karenk External
Since: Aug 25, 2009 Posts: 1

Posted: Tue Aug 25, 2009 5:01 am Post subject: RE: How do I combine ISNA and IF functions [Login to view extended thread Info.] Archived from groups: per prev. post (more info?) 


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.
"Teethless mama" wrote:
> > =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" wrote:
>
> > Try
> >
> > =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation
> > 1'!K3584),"",1)
> >
> > 
> > If this post helps click Yes
> > 
> > Jacob Skaria
> >
> >
> > "ottodesque" wrote:
> >
> > > 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 

Back to top 



