Even though Google Sheets has some handy features like SUMIF, IFNA, and IF CONTAINS functions that make your tasks incredibly easy, one of the most useful functions is the IF THEN formula. This function lets you check a condition and then choose what to do based on whether it’s true or false.
Let’s make this easier to understand with an example. Imagine you’re a teacher grading exams in Google Sheets. You want a quick way to mark whether a student passed or failed. You can use the Google Sheets IF THEN formula for this. If a student’s score is higher than a certain value, you want it to say “Pass.” Otherwise, it should say “Fail.”
In simple terms, the IF THEN formula in Google Sheets helps you make decisions in your spreadsheet. It’s like saying, “If this condition is met, do this; otherwise, do that.”
In today’s Google Sheets tutorial, you’ll learn everything there is to know about Google Sheets IF Then formula. We will not only explain the concept but go over some practical examples together.
But before we get hands-on with today’s tutorials, let’s quickly unveil the Google Sheets IF THEN formula syntax. Knowing this will make your work incredibly easy.
Understanding Google Sheets If Then Formula Syntax
Now that we’ve got a grasp of what the Google Sheets IF THEN formula does; let’s break down its basic structure. You can write it like this:
IF(logical_test, value_if_true, value_if_false)
In this formula, “logical_test” is the condition we’re checking. It’s basically a statement or a reference to a cell that gives us a “yes” or “no” answer, like “TRUE” or “FALSE.
The “value_if_true” part is what the formula will give us if the condition turns out to be true.
And then there’s the “value_if_false” part, which is optional. If you don’t fill it in and the condition isn’t met, the formula will give you a “FALSE” result.
So, in simple terms, the IF THEN formula helps you make decisions in your spreadsheet. It asks a question (the logical test) and gives you different answers based on whether the answer is “yes” or “no.” If it’s “yes,” you get one thing; if it’s “no,” you get something else. Easy, right?
Google Sheets If Then Formula (Step-by-Step Guide)
Having provided you with some background on the Google Sheets IF THEN formula, as well as analyzed the syntax for this formula, we now want to dive straight into the practical aspect of this tutorial.
In this section, we will unveil several examples so you have a complete understanding of precisely what the Google Sheets IF THEN formula entails.
Let’s look at our first example.
#Example 1: Grading Students’ Exam Results
In this example, we will illustrate how to use the Google Sheets IF THEN formula to automatically assess and categorize students’ exam results. By examining the performance of five students, Alice, Bob, Carol, David, and Emma, based on their exam scores, attendance, and homework completion.
Our objective: For clarity purposes, this example will show you how to:
- Streamline the grading process in Google Sheets.
- Automatically label students as “Pass” or “Fail” according to specific criteria.
- Consider multiple factors, such as exam scores, attendance, and homework completion, to make comprehensive grading decisions.
Here are the grading criteria we will use to make our grading decision:
- If a student’s exam score is 60 or higher (C4 >= 60).
- If a student’s attendance is 90% or more (D4 >= 90%).
- If a student has completed their homework (E4 = “Yes”).
Through this practical example, you will gain a clear understanding of how the IF THEN formula can be applied to efficiently evaluate and categorise student performance in your own educational or data analysis projects.
For this example, we will use the following sample data.
Copy Sample Data
To follow along, please copy our sample Google Sheets document via the link below.
If you already have your own data, you can replicate the steps on your sheet for a hands-on learning experience. Let’s get started.
Step 1: Choose A Blank Cell
The first thing we need to do is select the cell where we want our grading result to be generated. For this particular example, we will select cell F4. If you’re following along, select that cell and proceed to the next step we will discuss shortly.
Step 2: Add the Grading Formula
Now that you’ve chosen the cell where you want to see the student’s exam status, let’s get into the nitty-gritty of actually adding the IF THEN formula.
To do this, navigate to the formula bar, which is usually located just below the toolbar or at the top of your Google Sheets window. It’s the place where you can enter and edit formulas.
Here, you want to type in the following formula:
=IF(AND(C2 >= 60, D2 >= 90%, E2 = “Yes”), “Pass”, “Fail”)
Breaking down the formula:
In this formula:
- C4 represents the cell containing the exam score for the specific student.
- D4 represents the cell containing the attendance percentage for that student.
- E4 represents the cell indicating whether the student completed their homework.
What this formula does is check multiple conditions:
- It checks if the exam score is 60 or higher.
- It checks if the attendance percentage is 90% or more.
- It checks if the student completed their homework (indicated as “Yes”).
Step 4: Press Enter
After typing the formula, press the Enter key. Google Sheets will process the formula, and the result will appear in the selected cell.
If you did everything exactly as we showed you, your spreadsheet should look something like this:
Step 6: Generating Results for Other Students
In the screenshot you see above, you might have noticed that we’ve only calculated the result for the first student.
Now, it’s time to do the same for all the other students in an efficient way. But don’t worry; you don’t have to go through the hassle of doing this one student at a time.
We can use Google Sheets’ “Auto Fill” feature to generate results for the rest of the students automatically.
Here’s how to do it:
- Click on the cell where you’ve just calculated the result for the first student. You’ll see a small cross in the bottom-right corner of the selected cell.
- Click and drag this small cross down to cover all the cells corresponding to the other students in your list.
- Release the mouse button. Google Sheets will automatically apply the same formula to all the selected cells, adjusting the references for each student’s data (like their exam score, attendance, and homework completion).
In case you’re a bit confused, the video below provides better insight on exactly what you should do:
Voilà! You’ve now efficiently calculated and labelled the results for all your students without the need to enter the formula for each one manually. This is a time-saving trick that Google Sheets offers to make your work much easier.
After executing all the steps we discussed above, here is what our spreadsheet now looks like:
#Example 2: Using The If Then Statement With Calculations
Now, let’s explore how to take your Google Sheets skills to the next level. In our previous example, we learned how to use the Google Sheets IF THEN formula to grade students based on specific criteria.
But did you know that you can also use IF THEN for calculations? This means that when specific conditions are met, the formula can perform calculations and provide results.
Here’s a unique scenario to illustrate this concept:
Imagine you’re a teacher again, and you’ve recorded your students’ midterm and final exam scores in Google Sheets. To calculate the final grades, you want to give more weight to the final exam scores, say 60% of the final grade, and the midterm scores should account for the remaining 40%. The pass mark is still 60.
Our objective: We will use the Google Sheets IF THEN formula to calculate their final grades based on the weighted average of their midterm and final exam scores. The pass mark is 60, and we’ll consider midterm scores as 40% and final exam scores as 60% of the final grade. Students who score 60 or higher will pass, while those below 60 will fail.
Here is the sample data we will use for this example:
Let’s break down how to use the IF THEN formula to calculate the final grades:
Step 1: Select A Blank cell
First things first, we need to choose a blank cell. This is the cell where we want the final grade for the first student to appear. For this example, we will use cell D5. If you’re following along, just go ahead and select that cell in your spreadsheet.
Step 2: Enter The Formula
Having selected the cell where we want our final grade to be generated, we need to enter the formula, which is pretty easy. All you need to do here is navigate to the formula bar, which is just underneath the toolbar section of your Google Sheets window.
Here, you want to type in the following formula:
=IF((B5*0.4 + C5*0.6) >= 60, “Pass”, “Fail”)
Breaking down the formula:
- B5 represents the cell containing the midterm exam score for the first student.
- C5 represents the cell containing the final exam score for the first student.
This formula calculates the weighted average of the midterm (40%) and final exam (60%) scores for each student. If the result is 60 or higher, it will label the student as “Pass”; otherwise, it will label them as “Fail.”
Step 3: Press Enter
After entering the formula as instructed above, all that is left to do is press the Enter button. What this does is instruct Google Sheets to generate the result in the selected cell.
If you followed the instructions exactly as we showed you, your spreadsheet should look exactly like this:
Step 4: Apply the Formula to Other Students
In the screenshot you saw earlier, we successfully used the Google Sheets IF THEN formula to calculate the final grade for the first student.
Now, let’s save time and avoid doing this manually for each student. We’ll use Google Sheets’ “Auto Fill” option to generate final grades for all the other students automatically.
Here’s how it’s done:
- Click on the cell where you’ve just calculated the final grade for the first student. This is the cell that contains the formula.
- In the bottom-right corner of the selected cell, you’ll see a small cross. Click on this small cross, hold the mouse button, and drag it down to cover all the cells corresponding to the other students in your list. You’ll see a preview of the formula being applied to each cell as you drag.
- Once you’ve covered all the cells for the other students, release the mouse button.
Google Sheets will work its magic. It will automatically apply the same formula to all the selected cells, adjusting the references for each student’s data (midterm and final exam scores) as needed.
If this sounds a bit confusing, the video below will provide better clarification on how to execute this step:
There you have it. You’ve efficiently calculated and labeled the final grades for all your students without the need to enter the formula for each one manually. This is a time-saving feature in Google Sheets that makes your work much easier and more efficient.
Looking at everything we have covered so far, you’ll agree that using Google Sheets IF then formula to calculate final grades in Google Sheets is pretty easy.
Note: The examples we’ve explored in this guide today only scratch the surface of what you can do with the Google Sheets IF THEN formula. There’s a vast array of other scenarios and applications where this formula can prove incredibly valuable and versatile. So, don’t stop here—keep exploring and discovering new ways to leverage this powerful tool in your Google Sheets projects
Final Thoughts
In this guide, we’ve delved into the versatile world of the Google Sheets IF THEN formula, demystifying its application for both beginners and more experienced users.
We’ve explored how this formula can be employed to automate tasks, make decisions, and streamline processes using real-life scenarios such as grading students and calculating final grades.
By understanding the fundamental syntax of the IF THEN formula, exploring practical examples, and harnessing features like “Auto Fill,” you’ve gained valuable insights into harnessing the power of Google Sheets for efficient data analysis and management.
Remember that the IF THEN formula is just the beginning. Google Sheets offers a multitude of functions and tools that can be combined and customized to suit your specific needs. As you continue your journey with spreadsheets, keep experimenting and adapting these techniques to a wide range of projects, whether they involve data analysis, budgeting, project management, or more.
By building on the foundation laid out in this guide, you’re well on your way to becoming a proficient Google Sheets user, equipped with the skills to tackle complex tasks and unlock the full potential of this powerful spreadsheet tool.