I found out that you can't use an array formula if you are using the "if" "and" "or" formula on google spreadsheet. Can someone please help me fix this formula I made, if possible I still want it to be an array formula

=ArrayFormula((IF(AND(H7:H="NSW",K7:K>=59,M7:M="Yes",OR(N7:N="Yes",O7:O="Yes"),OR(P7:P="Yes",P7:P="Unsure"),OR(Q7:Q="Yes",Q7:Q="Unsure"),S7:S="Yes",OR(T7:T="Yes",T7:T="Unsure"),OR(U7:U="No",U7:U="Unsure"),OR(V7:V="No",V7:V="Unsure"),OR(W7:W="No",W7:W="Unsure"),OR(X7:X="No",X7:X="Unsure"),OR(Y7:Y="No",Y7:Y="Unsure"),OR(Z7:Z="No",Z7:Z="Unsure")), "Passed", "Failed")))

Here is also the link to the spreadsheet: https://docs.google.com/spreadsheets/d/1F6P7oynTDzckDFMd279ON1udFYWSfo3gUX1Kw2RjDbE/edit?usp=sharing

If you remove the word arrayformula, it work just perfectly fine but I'm using this formula for google sheet responses so I would really need it to be an array formula

CodePudding user response：

You haven't provided a link to the spreadsheet, so what I'll share below is constructed by eye and is not tested. But you can try this:

=ArrayFormula( IF( (H7:H="NSW") * (K7:K>=59) * (M7:M="Yes") * ((N7:N="Yes") (O7:O="Yes")) * ((P7:P="Yes") (P7:P="Unsure")) * ((Q7:Q="Yes") (Q7:Q="Unsure")) * (S7:S="Yes") * ((T7:T="Yes") (T7:T="Unsure")) * ((U7:U="No") (U7:U="Unsure")) * ((V7:V="No") (V7:V="Unsure")) * ((W7:W="No") (W7:W="Unsure")) * ((X7:X="No") (X7:X="Unsure")) * ((Y7:Y="No") (Y7:Y="Unsure")) * ((Z7:Z="No") (Z7:Z="Unsure")), "Passed", "Failed") )

In array formulas, the equivalent of AND is *; and the equivalent of OR is .