Jun 21 2021 02:47 PM
Trying to create an IF statement in Excel when all answers are YES, Column I turns to "YES" and if there is one answer that is "No" then column I turns to "NO".
Jun 21 2021 03:09 PM
SolutionThat's like
=IF( PRODUCT( (C4:C9="yes")*1) *
PRODUCT( (E4:E9="yes")*1) *
PRODUCT( (G4:G7="yes")*1) ,
"yes", "no")
If you are on 365 you don't need PRODUCT
Jun 21 2021 03:21 PM
Jun 22 2021 01:23 AM
@HighFiveEm , you are welcome
Jul 12 2021 08:56 AM
So I want to update it also if "Yes" or "N/A" then it is Yes. But if "No" in any question then it returns a "NO".
Jul 12 2021 09:17 AM
Jul 12 2021 09:25 AM
Do you mean #N/A error returned by function or text "N/A" ?
Jul 12 2021 09:38 AM
In general you may check the opposite - if at least one No then No else Yes. Like
=IF(COUNTIFS(E4:E9, "No")+COUNTIFS(G4:G7, "No")+COUNTIFS(C4:C9, "No"), "No", "Yes")
if you prefer COUNTIFS()
Jul 12 2021 09:46 AM
Jul 12 2021 10:28 AM
Thank you. Does previous formula to check "no" works, or that could be other variants except "yes", "no" and "n/a" ?
Jul 12 2021 12:38 PM
Jul 12 2021 12:50 PM
@HighFiveEm , you are welcome
Mar 24 2022 09:42 AM
Mar 24 2022 12:53 PM - edited Mar 24 2022 12:54 PM
Mar 24 2022 01:10 PM
A new option, Naming your table data, one can select the columns with answers and then test for the presence of a "no"
= AND(CHOOSECOLS(data,2,4,6)<>"no")
There are other ways of doing this but CHOOSECOLS is one of the latest.
Mar 24 2022 01:37 PM
The columns are just like you are showing in the example. I cannot get any formula to work. I have tried
=IF(AND(H8:I8="No")<>"no")
=AND(CHOOSECOLS(H8:I8="no")<>"no")
I get errors with both of them.
Mar 24 2022 02:02 PM
1) On which Excel version/platform you are?
2) Which exactly error do you have?
At least
=IF(AND(H8:I8="No")<>"no")
never works. AND() returns TRUE or FALSE and it never equals to "no"
Mar 24 2022 03:33 PM
My apologies for creating confusion. There were many aspects of traditional spreadsheets that I found distasteful, so my interest is now the new opportunities to create solutions that modern Excel offers. However, something similar in concept is possible using INDEX (I think it was Daniel Ferry that referred to the function as 'the Imposing Index').
= AND(INDEX(data,{1;2;3;4;5;6},{2,4,6})<>"no")
Dressed up, even that could be written
= LET(
status, INDEX(data,{1;2;3;4;5;6},{2,4,6}),
passes, status<>"no",
AND(passes)
)
which is more open to step by step testing.
Jun 21 2021 03:09 PM
SolutionThat's like
=IF( PRODUCT( (C4:C9="yes")*1) *
PRODUCT( (E4:E9="yes")*1) *
PRODUCT( (G4:G7="yes")*1) ,
"yes", "no")
If you are on 365 you don't need PRODUCT