| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combining ISNA and IF formulas
I am trying to write a formula that returns a value of blank if the contents
of field All Audit Compilation 1'!K3584 is either NA or P and the value of 1 if it is not NA or P. I started with =IF(ISNA('All Audit Compilation 1'!K3584),"") but still get the value of false if the field is blank and true if it contains NA. I have the formula IF('All Audit Compilation 1'!K3585="P",,"1") which works fine on its own but do not know how to combine the two formulas and have =IF(ISNA('All Audit Compilation 1'!K3584),"") return a value other than true or false |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining ISNA and IF formulas
Hi,
Try something like =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation 1'!K3584)),"",'All Audit Compilation 1'!K3584) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ottodesque" wrote: I am trying to write a formula that returns a value of blank if the contents of field All Audit Compilation 1'!K3584 is either NA or P and the value of 1 if it is not NA or P. I started with =IF(ISNA('All Audit Compilation 1'!K3584),"") but still get the value of false if the field is blank and true if it contains NA. I have the formula IF('All Audit Compilation 1'!K3585="P",,"1") which works fine on its own but do not know how to combine the two formulas and have =IF(ISNA('All Audit Compilation 1'!K3584),"") return a value other than true or false |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining ISNA and IF formulas
Returns value NA
"Shane Devenshire" wrote: Hi, Try something like =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation 1'!K3584)),"",'All Audit Compilation 1'!K3584) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ottodesque" wrote: I am trying to write a formula that returns a value of blank if the contents of field All Audit Compilation 1'!K3584 is either NA or P and the value of 1 if it is not NA or P. I started with =IF(ISNA('All Audit Compilation 1'!K3584),"") but still get the value of false if the field is blank and true if it contains NA. I have the formula IF('All Audit Compilation 1'!K3585="P",,"1") which works fine on its own but do not know how to combine the two formulas and have =IF(ISNA('All Audit Compilation 1'!K3584),"") return a value other than true or false |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining ISNA and IF formulas
That won't work. When the cell contains an error then the test for K3584="P"
returns the error and blows up the OR condition. You have to first test for the error: =IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation 1'!K3584="P",'All Audit Compilation 1'!K3584,"")) -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Try something like =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation 1'!K3584)),"",'All Audit Compilation 1'!K3584) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ottodesque" wrote: I am trying to write a formula that returns a value of blank if the contents of field All Audit Compilation 1'!K3584 is either NA or P and the value of 1 if it is not NA or P. I started with =IF(ISNA('All Audit Compilation 1'!K3584),"") but still get the value of false if the field is blank and true if it contains NA. I have the formula IF('All Audit Compilation 1'!K3585="P","","1") which works fine on its own but do not know how to combine the two formulas and have =IF(ISNA('All Audit Compilation 1'!K3584),"") return a value other than true or false |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining ISNA and IF formulas
I think we are getting closer. Here is what I have found.
=IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation 1'!K3584="P","",1)) STILL COUNTS NA AS 1 =IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation 1'!K3584="P",'All Audit Compilation 1'!K3584,"")) COUNTS P AS P AND DOES NOT RETURN THE VALUE OF 1 FOR ALL CELL CONTENTS OTHER THAN P OR NA "T. Valko" wrote: That won't work. When the cell contains an error then the test for K3584="P" returns the error and blows up the OR condition. You have to first test for the error: =IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation 1'!K3584="P",'All Audit Compilation 1'!K3584,"")) -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Try something like =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation 1'!K3584)),"",'All Audit Compilation 1'!K3584) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ottodesque" wrote: I am trying to write a formula that returns a value of blank if the contents of field All Audit Compilation 1'!K3584 is either NA or P and the value of 1 if it is not NA or P. I started with =IF(ISNA('All Audit Compilation 1'!K3584),"") but still get the value of false if the field is blank and true if it contains NA. I have the formula IF('All Audit Compilation 1'!K3585="P","","1") which works fine on its own but do not know how to combine the two formulas and have =IF(ISNA('All Audit Compilation 1'!K3584),"") return a value other than true or false |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Combining ISNA and IF formulas
=IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation
1'!K3584="P","",1)) STILL COUNTS NA AS 1 Ok, we need to clarify something. Are you wanting to test for the specific *ERROR* #N/A or the *TEXT* value NA? #N/A and NA are not the same. The ISNA function tests for the specific #N/A error. See if this is what you want. Testing for the *TEXT* value "NA" then you can use OR: =IF(OR('All Audit Compilation 1'!K3584={"NA","P"},"",1) Note that an empty cell will return 1. Is that what you want? If not: =IF(OR('All Audit Compilation 1'!K3584={"","NA","P"},"",1) -- Biff Microsoft Excel MVP "ottodesque" wrote in message ... I think we are getting closer. Here is what I have found. =IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation 1'!K3584="P","",1)) STILL COUNTS NA AS 1 =IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation 1'!K3584="P",'All Audit Compilation 1'!K3584,"")) COUNTS P AS P AND DOES NOT RETURN THE VALUE OF 1 FOR ALL CELL CONTENTS OTHER THAN P OR NA "T. Valko" wrote: That won't work. When the cell contains an error then the test for K3584="P" returns the error and blows up the OR condition. You have to first test for the error: =IF(ISNA('All Audit Compilation 1'!K3584),"",IF('All Audit Compilation 1'!K3584="P",'All Audit Compilation 1'!K3584,"")) -- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, Try something like =IF(OR('All Audit Compilation 1'!K3584="P",ISNA('All Audit Compilation 1'!K3584)),"",'All Audit Compilation 1'!K3584) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "ottodesque" wrote: I am trying to write a formula that returns a value of blank if the contents of field All Audit Compilation 1'!K3584 is either NA or P and the value of 1 if it is not NA or P. I started with =IF(ISNA('All Audit Compilation 1'!K3584),"") but still get the value of false if the field is blank and true if it contains NA. I have the formula IF('All Audit Compilation 1'!K3585="P","","1") which works fine on its own but do not know how to combine the two formulas and have =IF(ISNA('All Audit Compilation 1'!K3584),"") return a value other than true or false |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| combining formulas | Excel Worksheet Functions | |||
| combining formulas | Excel Discussion (Misc queries) | |||
| Help with combining 2 formulas | Excel Discussion (Misc queries) | |||
| combining formulas | Excel Discussion (Misc queries) | |||
| combining two formulas | Excel Worksheet Functions | |||
Linear Mode
