That’s all well and good once you know what you’re doing, but sometimes the learning process can be the real challenge. Lucky for you, we’ve got everything you need here in this article to master the basics of the IF function in Google Sheets.
What Does the IF Function Do?
The Google Sheets variant works similarly to Microsoft Excel IF functions. Basically, it calculates based on whether or not a value from a range is returned as TRUE or FALSE for specified criteria.
For example, you could use an IF function to put “Pass” into a cell if a person received above a certain grade. As in, it’s TRUE that the grade is above the specified percentage.
Let’s take a look at how the syntax works, then we’ll dive into exactly how to do the above example and a few others. The examples are going to be simplified, so they’re easy to follow. But, you can apply the same ideas to much larger and more complex spreadsheets.
Google Sheets IF Function Syntax
The syntax of a function in a spreadsheet program like Google Sheets is the bones of how it works. You could think of it kind of like an algebraic expression before you add the values in.
The syntax for the Google Sheets IF function goes as follows:
Here’s what each part of the syntax means:
IF tells Google Sheets which function you want to use. Logical_expression is the condition you want the IF function to check against. It must be able to return a TRUE or FALSE statement. Value_if_true is the value to return if the condition in the logical expression is met. Value_if_false is an optional parameter that tells Google Sheets what value to return if the condition is not met. If no value is specified, it will return FALSE.
Example 1: Creating a Simple Formula With the IF Function in Google Sheets
The simplest way to use the IF function is on its own with numerical values. So, let’s first look at such an example. Let’s pretend we’re the manager at a warehouse, and we’ve had to train our staff in a new workplace safety program that employees legally need to pass to continue working.
Considering the below data set where a passing mark would be 50/100 we could use the formula:
Then you would just have to click and drag the small blue box in the right-hand corner over the rest of the column to apply the formula to all the cells. Here’s how the formula is applied within the syntax.
B2>=50, is the logical_expression to show the condition that must be met. As >= is the operator in Google Sheets for greater than or equal to, the logical expression in simple terms is: If B2 is greater than or equal to 50.
“Pass”,”Fail” tells Google Sheets what to return if the logical_expression is TRUE or FALSE, respectively. In this example, anyone that has a test mark that is greater than or equal to 50 has Pass next to their name. Those that are below 50 have Fail.
Example 2: Nested IF Functions in Google Sheets
Nested IF functions are ones that perform a second IF function within the same formula. They essentially ask for a second condition if the first one returns a FALSE statement.
Let’s take a look at the same data set as before to make sense of this. This time, we are checking whether the employees are eligible for another shot at the test. Everyone that passed doesn’t need to take the test again, and those that got less than 40% must do some extra training first.
As you can see, even though Pete T failed the test, he still has No under the retraining column as his mark was under the 40% minimum to retake the test. John W, on the other hand, receives a Yes value.
Let’s take a look under the hood of the formula again:
B2>=50 is the same as before and is the logical_expression of the “nest” IF function “No” is the value to return if the condition is met. I. e. , those with a mark greater than or equal to 50 don’t have to retake the test. IF(B2<40, is the value_if_false for the first if function. It tells Google Sheets to perform a second if function if the value is not greater than or equal to 50. It now checks if the value is less than 40. “No”,”Yes” are the value_if_true and value_if_false for the nested IF function.
Here is a flow chart of how this calculation works for those of you that are still a little confused.
Example 3: Combining the IF Function With Other Functions
There are tons of ways to use the IF function with others to build specific formulas for your needs. One of the more common ones is the AND function. Doing this will allow you to check several criteria before returning a result.
We’ll use the same data again, but this time you need to pick a person to fire. Let’s say it has to be the person that failed the test AND is ineligible to retake the test.
We could use the following combination of the AND and IF functions to determine this.
The result would be to fire Pete T.
Let’s break down how the formula worked: The AND function is nested inside the IF function and works as the logical_expression. The rest of the function works the same as the other IF functions we’ve discussed already. So, the formula says, IF D2=”No” AND C2=”Fail” are TRUE, show “Yes” if the result is FALSE return “No”
There are also plenty of other IF functions that work with a secondary function, such as COUNTIF in Google Sheets. Those functions should be easy to conquer once you wrap your head around standard IF functions.
IF Functions 101
IF functions can really ramp up your spreadsheet game and work well within other functions. Now that you’ve followed along with our examples, we recommend you spend some time practicing what you’ve learned. See if you can come up with some of your own logical_expression and value_if_true combinations in some of your own worksheets.
