What’s a logical function?
A logical function in Google Sheets returns either a True or a False value depending on whether the logical expression you feed to it is true or not. One logical function that everyone should be familiar with is IF: you write a logical expression and tell Google Sheets to give you value 1 if the IF statement is true, and value 2 if the statement is false.
With that definition, let’s get started with our first logical function.
Everyone knows about the <p-code>IF<p-code> function, but not everyone knows about <p-code>IFS<p-code>. The <p-code>IF<p-code> function’s logic goes like this:
<p-code>=IF(logical_expression, value_if_true, value_if_false)<p-code>
The <p-code>IF<p-code> function is great if there is only one logical expression, such that there are only two outcomes, true or false. However, if there are multiple logical expressions, the formula can get messy real quick with a lot of nested <p-code>IF<p-code>s, like:
<p-code>=IF(logical_expression_1, value_if_true, IF(logical_expression_2, value_if_true, IF(logical_expression_3, value_if_true, value_if_false)))<p-code>
This is where <p-code>IFS<p-code> comes in handy. <p-code>IFS<p-code>’s logic handles multiple logical expressions in a more comprehensible way:
<p-code>=IFS(condition1, value1, [condition2, …], [value2, …])<p-code>
So instead of writing a formula like
You can increase the legibility by writing:
The <p-code>IFS<p-code> function can help you understand others’ (and your own) spreadsheet more quickly, as well as reducing errors related to incorrectly nested logic. The rule of thumb in deciding whether to use <p-code>IFS<p-code> over <p-code>IF<p-code> is “always use <p-code>IFS<p-code> unless there are no nested <p-code>IF<p-code> statements”.
Note that the <p-code>IFS<p-code> function evaluates each logical expression sequentially and stops immediately after finding a true logical expression. So the order in which you write out your logical expressions is important.
For example, in the <p-code>IFS<p-code> statement above, the second-to-last logical expression:output value pairing is <p-code>$c11>0<p-code>:<p-code>”1-100”<p-code>. If this pairing’s position was ahead of <p-code>$c11>300<p-code>:<p-code>”301-364”<p-code> in the formula, the output value for any number between 301 and 364 would be “<p-code>1-100<p-code>”, and not “<p-code>301-364<p-code>” as we intended. This is because technically any number between 301 and 364 is greater than 0 (i.e. logical expression is true), and Google Sheets stops evaluating the rest of the logical expressions. I know it’s a bit confusing, but compare the formulas in cells <p-code>D11<p-code> (correct order of logical expressions) and <p-code>E11<p-code> (reversed order) in this sheet, and it should be clear.
Here’s an example comparing <p-code>IF<p-code> to <p-code>IFS<p-code>.
2. AND / OR
<p-code>AND<p-code> and <p-code>OR<p-code> functions come in handy when you are trying to construct complex logical arguments in combination with <p-code>IF<p-code> and <p-code>IFS<p-code>.
The <p-code>AND<p-code> function is only true if all of the logical arguments nested inside the function are true, and denoted as:
<p-code>=AND(logical_expression_1, logical_expression_2, ...)<p-code>
The <p-code>OR<p-code> function is true if any of the logical arguments are true.
<p-code>=OR(logical_expression_1, logical_expression_2, ...)<p-code>
When you combine these functions, you can create powerful logical queries like:
In plain English, the above formula is saying:
If the listing’s neighbourhood_group is Manhattan OR neighbourhood is Williamsburg AND its price is below $200, mark the listing as a “YES”; otherwise, leave blank.
Listing ID 2539 doesn’t meet the criteria because it’s in Brooklyn (not Williamsburg), and neither does ID 2595 because its price is over $200. ID 3647 is in Manhattan and the price is below $200, so it gets a “YES”.
The <p-code>SWITCH<p-code> function evaluates an expression and returns one of multiple values which are pre-set by you. This function is similar to <p-code>IFS<p-code> in that there are multiple outcomes based on an evaluation. The main difference is that in <p-code>IFS<p-code>, you’re evaluating a logical expression and returning a value if that logical expression is true; in <p-code>SWITCH<p-code>, you’re evaluating an expression with multiple outcomes and returning a value based on it. The syntax for the <p-code>SWITCH<p-code> function is:
<p-code>=SWITCH(expression, case1, value1, case2, value2, ..., default)<p-code>
This function is much easier to understand with an example:
In the above example, I wrote a formula that evaluates the neighbourhood of a listing and assign a rating based on which neighbourhood it’s in. Note that I’m not evaluating whether the listing is in a certain neighbourhood, because that would be a logical expression and I’d be better off using the IFS function instead. Below are the neighbourhood-rating pairings:
- Good: Hell’s Kitchen and Midtown
- Great: Murray Hill and Bedford-Stuyesant
- It’s still NYC!: Everywhere else
The last pairing for everywhere else is the default or fallback value for SWITCH that it reverts to if the expression it is evaluating doesn’t match any of the specified pairings. Note that unlike <p-code>SWITCH<p-code>, the <p-code>IFS<p-code> function doesn’t have a default value. For example, we didn’t assign a rating to Kensington, so listing ID 2539 defaults to “It’s still NYC!”
4. IFERROR / ERROR.TYPE
<p-code>IFERROR<p-code> is a shorthand function that is equal to <p-code>IF(ISERROR(value))<p-code>. It evaluates a value and displays it if it’s not an error; if it is an error, it either shows the cell as a blank (default) or another value if you specify it. Here’s the syntax for <p-code>IFERROR<p-code>:
Below is an example that shows Month-over-Month revenue growth. In the “MoM Revenue Growth %” row (unhighlighted), you can see two errors: <p-code>#VALUE!<p-code> and <p-code>#DIV/0!<p-code>. The <p-code>#VALUE!<p-code> error is due to trying to get the growth rate for the first month (i.e. dividing $3,000 in cell <p-code>C5<p-code> by the text value “Monthly Revenue” in cell <p-code>B5<p-code>). The <p-code>#DIV/0!<p-code> error is because I’m trying to divide $8,000 by zero.
These error values are problematic, because any subsequent calculations involving them also result in an error value. They are also distracting and look unprofessional.
You can clean up these errors by using <p-code>IFERROR<p-code>:
Now there are blanks instead of error values because I wrapped an <p-code>IFERROR<p-code> function around the expression. I could have also opted to show a custom error message like “ERROR” by including the optional value like this:
Or…I could make it really fancy by showing different error messages based on the type of error by using <p-code>ERROR.TYPE<p-code>.
What’s an ERROR.TYPE?
There are 8 different error types in Google Sheets. Going into each feels out of scope for this post, but you can refer to this excellent writeup by Info Inspired to learn more.
For the purposes of this post, we can combine what we learned in <p-code>SWITCH<p-code> with <p-code>ERROR.TYPE<p-code> to show different messages based on the error, like this:
In plain English, the formula above is saying:
Show the MoM growth rate by dividing this month’s revenue by the previous month’s revenue and then subtracting 1. If there is an error, get the error type. If the error type is 2 (i.e. <p-code>#DIV/0!<p-code>), then show “Prev month was $0”. If the error type is 3 (i.e. <p-code>#VALUE!<p-code>), show “First month”. For all other errors, show a generic error message “Error - check formula”.
Remember how I said <p-code>IFERROR = IF(ISERROR(expression))<p-code>? <p-code>ISBLANK<p-code> is like <p-code>ISERROR<p-code>, but it evaluates whether a cell is blank or not and returns a true / false value. I use <p-code>ISBLANK<p-code> in combination with <p-code>IF<p-code> (or <p-code>IFS<p-code>) a lot, because Google Sheets doesn’t have an <p-code>IFBLANK<p-code> function. So you have to write out <p-code>IF(ISBLANK(expression))<p-code> unlike <p-code>IFERROR<p-code>.
<p-code>ISBLANK<p-code> is super useful, because calculations involving blank cells do not result in an error (unlike zero or text values). But there are times when you want to treat blank values differently to avoid inadvertently making mistakes in your calculation. Let’s use the revenue growth example from the previous section to illustrate this point. If we’re launching a marketing campaign and expect our MoM growth rate to increase by 2x:
Recap: Our MoM revenue growth for the first month was causing an error in the previous section so we used the <p-code>IFERROR<p-code> function to show a blank cell instead. Now, when we multiply that blank cell by 2, the new value will be 0 (cell <p-code>C7<p-code>) because Google Sheets treats blank cells like zeros. To account for the marketing campaign’s impact, we can instead write a formula to hardcode a growth rate (50% in this case) like this:
This ensures that we’ll either 2x the previously stated growth rate or if the cells is blank, assume the growth rate will be 50%. As you can see in the screen capture above, the resulting new monthly revenues can differ quite a lot ($16,667 vs. $25,000) depending on whether you’ve accounted for blank values.
In this post, we covered logical functions in Google Sheets that ops should know:
- <p-code>IFS<p-code>: We talked about when to use <p-code>IFS<p-code> over <p-code>IF<p-code> (i.e. when there are multiple logical expressions).
- <p-code>AND / OR<p-code>: We covered how to combine <p-code>AND<p-code> or <p-code>OR<p-code> functions together with <p-code>IF<p-code> statements to build complex logic into our formulas.
- <p-code>SWITCH<p-code>: We learned about the <p-code>SWITCH<p-code> function, which is similar to IFS except it handles expressions that could output multiple values vs. a logical expression which has only 2 possible values.
- <p-code>IFERROR / ERROR.TYPE<p-code>: We explored the <p-code>IFERROR<p-code> function and how we can use it to get ahead of any errors that may occur in Google Sheets. We briefly touched on the <p-code>ERROR.TYPE<p-code> function as well and how we could use it to customize our error messages.
- <p-code>ISBLANK<p-code>: Lastly, we introduced the <p-code>ISBLANK<p-code> function, which is often used together with an <p-code>IF<p-code> statement, and discussed why it’s good practice to write a formula that handles blank values when you’re working with Google Sheets.
If you’re interested in learning more on your own, here’s a comprehensive list of all Google Sheets functions.