Nested IF statement with AND

[YNWA]

Hi, I need help with a nested IF statment.

I have the following which currently works

=IF(AND($H$4="Complient",H65="205(3a))"),1,IF($H$4="Not Complient",H65="205(3a)))",1))

This works and returns a value of 1 when Compliment is found.

This is looking in H4 for Compliment and H65 for the text "205(3a)". It works fine.

The only problem is I have about 90 celles to put this in and each time the text part is different. The text is the same as in say cell A91 then the next formula the text is the same in cell A93 etc...

So what I ideally want is the "205(3a)" to be replace by H65=A91, then I can just copy the formula down and the A91 will update itself.

However when I put in A91, I get a return of FALSE instead of 1.

=IF(AND($H$4="Complient",H93=A93),IF($H$4="Not Complient",H93=A93),1)

^^^ that is what I have put in for the value to return FALSE.

I need the 1 to show so I can then perform counts and sums on the figure.

Any ideas?

Thanks
Will

 

[stopher]

=IF(AND($H$4="Complient",H93=A93),IF($H$4="Not Complient",H93=A93),1)

^^^ that is what I have put in for the value to return FALSE.

This is logically very different from your first expression. Moreover you will only get a result of 1 if H4 is both "Compliant" and "Not Compliant" at the same time. Since this is never going to happen, the result will always be false.

Maybe you could explain the logic you want in words.

Chris

 

[YNWA]

This is logically very different from your first expression. Moreover you will only get a result of 1 if H4 is both "Compliant" and "Not Compliant" at the same time. Since this is never going to happen, the result will always be false.

Maybe you could explain the logic you want in words.

Chris

I have 2 tabs.

If a cell C4 in Adult tab= Y then cell B4 in Sheet3 should say Complient.
If a cell C4 in Adult tab= NA then cell B4 in Sheet3 should say N/A
If a cell C4 in Adult tab= N then cell B4 in Sheet3 should say Not Complient

For which I have this in cell B4 on Sheet3

=IF(Adult!C4="Y","Complient", IF(Adult!C4="NA", "N/A", IF(Adult!C4="N", "Not Complient")))

This works.

Then in Column A, I have 90 odd question numbers, but they are set out like such 101, 101(1a), 101(2a), 101(2b) etc...

In cell A5 I have 101(2b) as the question number.

I need the cells next to this in column B to find it and show values based on a few things.

In cell B5 I have

=VLOOKUP(A$5,Adult!$D$4,1,0)

Which is looking at cell A5 for to see if what is in A5 appears in Adult tab in cell D4. If it does then I want the cell B5 to show the question number. So it find 101(2b) and shows in. This works

Then underneath I have cell B6 with this in

=IF(AND(B4="Complient",B5="101(2b)"),1,IF(B4="Not Complient",B5="101(2b)",1))

Which is where I am stuck.

I want this cell to look to see if B4 = Compliment and B5="101(2b), but if B4=Not Complient and B5=101(2b) I want a TRUE appear. And if B4=N/A and B5=101(2b) I want it to show a 1.

Currently if Complient is shown then I get a 1 showing, if NA is shown I get a 1 showing and if Not Compliment is shown I get a TRUE showing.

However I want to take out the "101(2b)" part of the formula and replace it with the cell number, so I can easily copy the formula down and update this part automatically.

Then I can do counts and countifs to count the number of 1's to help with other calculations.

Does this make sense?

Cheers
Will

 

[NBVC]

Does this work for you?

=IF(H65=A93,IF(OR($H$4="Complient",ISNA($H$4)),1,IF($H$4="Non Complient",TRUE,"")),"")

 

[YNWA]

Does this work for you?

=IF(H65=A93,IF(OR($H$4="Complient",ISNA($H$4)),1,IF($H$4="Non Complient",TRUE,"")),"")

Hi,

Yes it works but when H4 shows N/A I need the cell to show 1 instead of being blank.

I think the problem with N/A is that I am not creating an error, I am just asking for the cell H4 to output "N/A". So I dont think the ISNA function would pick up on that.

Thanks
Will

 

[NBVC]

I assumed you had the #N/A! error in H4 since VLOOKUP() would return that error if you have no match.....

Anyway, if you have text string "N/A", try then:

=IF(H65=A93,IF(OR($H$4="Complient",$H$4="N/A"),1,I F($H$4="Non Complient",TRUE,"")),"")

 

[YNWA]

I assumed you had the #N/A! error in H4 since VLOOKUP() would return that error if you have no match.....

Anyway, if you have text string "N/A", try then:

=IF(H65=A93,IF(OR($H$4="Complient",$H$4="N/A"),1,I F($H$4="Non Complient",TRUE,"")),"")

Yeah if not answer is filed (which means they have not selected Y, N or NA from the drop down) the field H4 will show FALSE.

Now when I enter I get a #NAME? error when Not Complient and a 1 for Complient or N/A.

So I think thats should be fine. Would there be a way to get rid of the #NAME? error to show TRUE or a 0?

 

[NBVC]

It seems the forum added a space in my formula... should be:

=IF(H65=A93,IF(OR($H$4="Complient",$H$4="N/A"),1,IF($H$4="Non Complient",TRUE,"")),"")

 

[YNWA]

It seems the forum added a space in my formula... should be:

=IF(H65=A93,IF(OR($H$4="Complient",$H$4="N/A"),1,IF($H$4="Non Complient",TRUE,"")),"")

I noticed that the first time you posted, forgot to edit second time.

Cheers, all seems to be working fine.

 

[YNWA]

It seems the forum added a space in my formula... should be:

=IF(H65=A93,IF(OR($H$4="Complient",$H$4="N/A"),1,IF($H$4="Non Complient",TRUE,"")),"")

Would it be possible to add in a ISNA so it shows a blank cell if H65 is not found?

Currently its showing #N/A when H63 does not find itself in A93.

 

[YNWA]

I think it has something to do with my VLOOKUP.

I have in B5

=VLOOKUP($A5,Adult!$D$4,1,0)

This looks for A5 and if its found in AdultD4, shows it like 101(2b).

If not it shows #N/A

Thus if not found the cell below in B6 will #N/A. Which currently has this in

=IF($A5=B5,IF(OR($B$4="Complient",$B$4="N/A"),1,IF($B$4="Not Complient",0,"")),"")

I am trying to sum the 1's and 0's together but sum fails when #N/A is in the row. Is there a way around this?

 

[NBVC]

The formula I gave you:

=IF(H65=A93,IF(OR($H$4="Complient",$H$4="N/A"),1,IF($H$4="Non Complient",TRUE,"")),"")

will return a blank if H65 is not equal to A93, doesn't it?

Also not sure why you need Vlookup to lookup one cell?

You can use something simpler like

=IF($A5=Adult!$D$4,A5,"")

 

[YNWA]

The formula I gave you:

=IF(H65=A93,IF(OR($H$4="Complient",$H$4="N/A"),1,IF($H$4="Non Complient",TRUE,"")),"")

will return a blank if H65 is not equal to A93, doesn't it?

Also not sure why you need Vlookup to lookup one cell?

You can use something simpler like

=IF($A5=Adult!$D$4,A5,"")

I am using a VLOOKUP as some cells say =VLOOKUP(A5,Adult!$D$5:$D$21,1,0)

Also the formula you gave is good but I need to sum rows and it doesnt sum where I have a N/A in a cell.

It N/A's when the VLOOKUP doesn't find what its looking for.

 

[Brianwarnock]

I am using a VLOOKUP as some cells say =VLOOKUP(A5,Adult!$D$5:$D$21,1,0)

Also the formula you gave is good but I need to sum rows and it doesnt sum where I have a N/A in a cell.

It N/A's when the VLOOKUP doesn't find what its looking for.

No it doesn't , it #N/A and reading through this thread the N/A #N/A situation drove me bonkers.

Not sure what is going on it would have been simpler to see the sheeet but my instinct is that you need to tackle the problem at source and in your Vlookups use ISNA to remove the #N/A and replace it with something that you can handle later.

Brian