Forum Discussion
Trying to create an IF Statement in Excel if all answers are Yes the cell turn to "YES"
That'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
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.
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.
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.
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"