Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > How do I combine ISNA and IF functions
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 9
Default How do I combine ISNA and IF functions
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,520
Default How do I combine ISNA and IF functions
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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,718
Default How do I combine ISNA and IF functions
=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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,520
Default How do I combine ISNA and IF functions
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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 9
Default How do I combine ISNA and IF functions
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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 8,520
Default How do I combine ISNA and IF functions
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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 9
Default How do I combine ISNA and IF functions
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
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 3,346
Default How do I combine ISNA and IF functions
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
Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 2
Default How do I combine ISNA and IF functions
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
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
isna/iserror functions M Thompson Excel Discussion (Misc queries) 7 May 25th 09 01:20 AM
isna & if functions Lisa Excel Worksheet Functions 8 June 7th 06 08:56 PM
Combine functions? ~suky~ Excel Worksheet Functions 0 February 23rd 06 03:51 PM
ISNA(MATCH...) and IF functions - can they be combined? Robyn from Melbourne, Australia Excel Worksheet Functions 1 November 11th 05 05:18 AM
VLOOKUP and ISNA functions Steve ([email protected]) Excel Worksheet Functions 2 July 8th 05 11:59 PM


All times are GMT +1. The time now is 07:11 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright 2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement