CODEWITHSUNDEEP
google-sheetsgoogle-sheets-api
eiriinjye
eiriinjye

Reputation: 25

Convert this array formula for google spreadsheet

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

Upvotes: 1

Views: 259

Answers (1)

Erik Tyler
Erik Tyler

Reputation: 9345

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 +.

ADDENDUM (after additional comment and sharing of link to spreadsheet)

Erase everything from Column AB (including the header) and place this in cell AB1:

=ArrayFormula( IF(A:A="",, IF(ROW(A:A)=1, "Eligibility", IF( (H:H="NSW") * (K:K>=59) * (M:M="Yes") * ((N:N="Yes") + (O:O="Yes")) * ((P:P="Yes") + (P:P="Unsure")) * ((Q:Q="Yes") + (Q:Q="Unsure")) * (S:S="Yes") * ((T:T="Yes") + (T:T="Unsure")) * ((U:U="No") + (U:U="Unsure")) * ((V:V="No") + (V:V="Unsure")) * ((W:W="No") + (W:W="Unsure")) * ((X:X="No") + (X:X="Unsure")) * ((Y:Y="No") + (Y:Y="Unsure")) * ((Z:Z="No") + (Z:Z="Unsure")), "Passed", "Failed") ) ) )

This will produce the header and all results. Blank rows will be left blank. Current results are checked and correct.

It's best to use a whole column approach (e.g., A:A in the top cell instead of A2:A in the second cell) since the sheet that contains the formula is using a QUERY to bring in that information and in case you ever decide to use sorting.

Upvotes: 2

Related Questions