|
|
| Next: when field contains |
| Author |
Message |
ottodesque
External
Since: Apr 24, 2009
Posts: 4
|
 Posted: Thu Jul 09, 2009 8:01 am 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 8:17 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?) |
|
|
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 8:27 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?) |
|
|
> =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 8:42 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?) |
|
|
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 8:58 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?) |
|
|
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 9:09 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?) |
|
|
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 9:16 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?) |
|
|
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 9:30 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?) |
|
|
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 1: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 |
|
|
|
|
|
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
|
| |
|
|
Office