Google Sheets Functions – OR & AND

With the IF function we usually check if one condition is true or false, but what happens if we want to check multiple conditions? This is where the OR or AND functions come in handy. With OR we can check if one or other conditions are true, and with AND we can check if all the conditions are true.

We’ll start with how the function works then look at some practical examples of both.


Example 1 – How does the OR function work? And how does it work with IF?

Let’s first look at how the OR function works. Here we have two numbers in cells A1 and A2 and we want to know if either number they equals 10.

functions5-2

In cell B1, we write the following formula:

functions5-1

This looks at the contents of cell A1 and decides if it’s equal to 10, and then looks at cell A2 and again decides if it equals to 10. If either cell has a 10 in it, then it will display TRUE. If there isn’t a 10 in either cell, then it will be FALSE.

So, in the example above, we have a 10 in cell A1, and correctly it displays TRUE in cell B1 meaning that it’s found a number 10 in one of the cells.

This is great, but displaying TRUE or FALSE, often doesn’t help us very much, and what we want is that if it’s TRUE, then the formula does something, and if it’s FALSE, it does something else.

So, quite often we wrap up the OR function in an IF function, to allow us to take action depending on whether it’s TRUE or FALSE. See my post on the IF function if this is new to you.

So we modify the formula like this:

=IF(OR(condition 1, condition 2), Action if true, Action if false)

functions5-3

We add IF and an open bracket before the OR function, then after it, we add a comma and the two actions we want to take. In this case, if the function finds a number 10, it displays the text “At least one is true”, and if it doesn’t, it displays the text “Both are false”.

functions5-4


Example 2 – Checking to see if at least one race time is below the target time

Let’s use the above formula in a practical example. Here we have some athletes who need to have run under 10.5s in the 100m in two pre-competition races, in order to be able to enter the competition. We have the times they ran in both races, then in column D, we add the OR formula to see if they can compete or not.

functions5-5

Here the formula in column D is similar as before, we start with an IF function, then the OR function.  Then we add the two conditions, in this case, a check to see if the value in column B is less than 10.5s or if the one in column C is less than 10.5s. If it finds that the athlete has run under 10.5s in one or more of the races, it states “Yes” they can compete in the competition. If they haven’t run under 10.5s, then it displays “No”.

functions5-6

So, we can see that Paul and John are ok to enter the competition.


Example 3 – Looking at more than two race times

In the example above we looked at two race times, but the OR function can look at lots of different values. Here the athletes have run three races.

functions5-7

The formula in column E is as before, except that we just add a third condition in the brackets.

functions5-8


Example 4 – Using the AND function

The AND function works in a similar way to the OR function. OR looks to see if 1 or more of the conditions are met, whereas, AND checks to see if ALL the conditions are met.

Here we have some students who have to get more than 60% in all three tests on the course, in order to pass the course. If they have, it displays “Passed”, if not they haven’t it displays “Failed”.

functions5-9

So, in place of the OR function, we add the AND one in column E.

functions5-10


Example 5 – Using AND to check if the values are within a range

Here we want to see which groups the students should be in. We’ve already put those who got 80% or more in group 1, and now we want to divide the remaining students into either group 2 or 3. Those who got more than 60% and less than 80% will go in group 2, and the rest will be in group 3.

functions5-11

To do this, we can use an AND function in column C. We look at the values in column B to see if they are more than 60% or less than 80%. If the test results meet this condition, then it displays “Group 2”, otherwise it displays “Group 3”.

functions5-12


Example 6 – Categorising the values based on certain conditions

In the example above, we had already allocated some students to Group 1, but this time let’s divide up the students into 3 groups all automatically.

functions5-13

First, in column C we start off with an IF function, and check for those who have 80% or more. If their test result is 80% or more, it displays “Group 1”. If the test is less, it moves onto the next IF statement.

This time it checks to see if the result is between 60 and 80%, as we saw in the example above. If it does, then it displays “Group 2”, if it doesn’t it shows “Group 3”.

functions5-14


Example 7 – Using functions within an OR or AND function

Introducing the WEEKDAY function

We can also use other functions in the OR function. Here we have some prices where if the work was carried out on a weekend, a weekend supplement is added.

We need to check to see if the date fell on a weekend, then based on whether that is TRUE or FALSE, we add a supplement or not.

functions5-15

In column B, we add this formula:

functions5-16

The WEEKDAY function looks at the date in column A and converts that into a number between 1 and 7. Sunday is 1 and Saturday is 7. So, if it reports a number that is a 1 or a 7, then the date is at the weekend.

In column E we then check to see if column B is TRUE or FALSE, if it’s TRUE we add the supplement, if not, we leave the price as it is.

functions5-17

We could combine the formulas in columns B and E by wrapping the OR function in an IF one:

So, we have if the date in column A is Saturday (7) or Sunday (1), it adds the price (C) and the supplement (D), if not it just displays the price (C).

functions5-21

Just as a little extra, if we wanted to check to see if it is a weekday, we could use an AND function. This checks to see if the date isn’t a Saturday (7) and isn’t a Sunday (1).

functions5-18


eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Flipboard / Twitter / Google+)


 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s