Using OR Logical Function to Return Expanded Results in Google Sheets

Do you want to learn how to use the OR logical function to return expanded results in Google Sheets? Look no further! In this tutorial, I will share some handy workarounds that will help you achieve just that.

The OR Logical to Return an Expanded Result in Google Sheets

There are several methods you can use to get expanded results when using the OR logical test within the IF function. Let’s explore them one by one.

1. OR Logical Expanded Result Using Nested IF

Let’s start with a simple example. Suppose you have some numbers in columns A and B, and you want to apply OR logical tests by nesting IF formulas. If the value in cells A2 or B2 is greater than 0, you want the formula to return TRUE; otherwise, it should return FALSE.

The non-array OR formula would look like this: =or(A2>0,B2>0)

To achieve this using nested IF formulas, you can use the following formula:
=if(A2>0,TRUE,IF(B2>0,TRUE))

Now, if you want to apply this formula in multiple rows, you can simply copy it down. But what if you want a single formula that can do the logical test in every row and populate an expanded array result? Enter the ArrayFormula function!

You can use the ArrayFormula function along with nested IF formulas to achieve this. Here’s an example:
=ArrayFormula(if(A2:A4>0,TRUE,IF(B2:B4>0,TRUE,FALSE)))

Similarly, you can use the ArrayFormula function to test whether a range contains weekends or workweeks. Here’s an example:
=ArrayFormula(if(A1:A="","",if(A1:A="Saturday","Weekend",if(A1:A="Sunday","Weekend","Workday"))))

Although this method works, I don’t recommend using nested IF formulas for OR logical tests in Google Sheets, as the formula can become complex and difficult to manage if more columns or arguments are involved.

2. Alternative to Nested IF for OR Array Expanded Result

Here’s another way you can use the OR logical function to return an expanded array result in Google Sheets. Instead of using nested IF formulas, you can replace the OR function with the addition (+) operator.

You can use the following formula:
=ArrayFormula(if((A2:A4>0)+(B2:B4>0)>0,TRUE,FALSE))

In this formula, I’ve only applied it in cell F2, but the result will expand to the rows below. Let me break down the formula for better understanding:

• A2>0 returns TRUE
• B2>0 returns TRUE
• (A2>0) + (B2>0) returns 2 (since TRUE is equivalent to 1 in numerical calculations)

To test whether a range contains weekends or workweeks, you can use the following formula:
=ArrayFormula(if(A1:A="",,if((A1:A="Saturday")+(A1:A="Sunday"),"Weekend","Workweek")))

3. OR Array Formula Using MAP Lambda – Recommended

Now let’s explore a new method using the MAP function. With this method, we can expand the formula =or(A2>0,B2>0) using the MAP function.

Here’s how you can use the MAP function to achieve this:
=map(A2:A4,B2:B4,lambda(a,b,or(a>0,b>0)))

To test whether a range contains weekends or workweeks using this method, you can use the following formula:
=map(A1:A,lambda(a, if(a="",,if(or(a="Saturday",a="Sunday"),"Weekend","Workweek"))))

And that’s it! You now know how to use the OR logical function to return expanded results in Google Sheets. These workarounds will surely come in handy when working with logical tests in your spreadsheets. Enjoy exploring the possibilities!

For more tips and tricks related to Google Sheets, visit Crawlan.com.