Help!

How do I combine ISNA and IF functions

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions RSS
Next:  when field contains  
Author Message
ottodesque
External


Since: Apr 24, 2009
Posts: 4



PostPosted: 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



PostPosted: 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



PostPosted: 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



PostPosted: 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



PostPosted: 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



PostPosted: 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



PostPosted: 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



PostPosted: 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



PostPosted: 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
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Worksheet Functions All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum