When it comes to using the AND logical function in an array in Google Sheets, things can get a little tricky. Unlike the OR logical function, you can’t simply nest it within an IF function to get expanded results. But fear not, my friends, for there is an alternative way to achieve the same results in Google Sheets. This knowledge is not just handy, it’s absolutely essential! So, let me show you how to master the art of using the AND logical in an array in Google Sheets.
The AND Logical Function in Expanding Arrays
Let’s say you want to return TRUE if the values in cell A2 and cell B2 are greater than 0. The typical AND logical test would involve using the following formula:
=if(and(A2>=0,B2>=0),TRUE,FALSE)
This logical test would simply return either TRUE or FALSE based on the values in the selected cells. But what if you want to expand the results to rows below? In other words, what if you want to test the values in A3 and B3, A4 and B4, and so on? That’s where using the AND logical in an array comes into play. Allow me to demonstrate with an example formula.
The formula above is an example of how to use the AND logical in an array in Google Sheets. I know it might seem a little complicated at first, but don’t worry, I’ll break it down for you.
AND Array Formula Explanation
Let’s assume that the value in cell A2 is 5 and the value in cell B2 is 6. In this case, the below formula in any cell would return 1.
Formula:
=(A2>0)*(B2>0)
Result:
TRUE x TRUE = 1However, when you want to use this formula in an array, it would look something like this:
=ArrayFormula(if(len(A2:A),(A2:A>0)*(B2:B>0),""))
Now, you might be wondering why I’ve used the IF and LEN functions in this formula. Well, the truth is, you can replace the IF+LEN combo with just if(A2:A<>"", because both serve the same purpose. But what exactly is that purpose?
The combination of IF and LEN functions in the formula is used to limit the expansion of the formula results based on the range. You see, we’ve used an infinite range in the formula, like A2:A, instead of using a fixed range like A2:A10 or something similar. The IF and LEN combo ensures that the formula only expands where there are values present within the specified range. If you need further clarification, feel free to check out my LEN function tutorial.
This is the only way you can effectively use the AND logical in an array in Google Sheets. But please note that the above formula will only return 1 for TRUE and 0 for FALSE, which is sufficient for logical tests. However, if you’re particular about obtaining a Boolean TRUE or FALSE, you can use the following AND Array Formula:
=ArrayFormula(if(len(A2:A),if((A2:A>0)*(B2:B>0)>0,TRUE,FALSE),""))
OR
=ArrayFormula(if(A2:A<>"",if((A2:A>0)*(B2:B>0)>0,TRUE,FALSE),""))
I hope you’ve found these tips helpful. Stay tuned for more awesome Google Spreadsheet tutorials coming your way soon.
