Are you struggling with IF Then calculations in Google Sheets? Don’t worry, we’ve got you covered! In this article, we’ll explain how to use IF Then formulas in Google Sheets and provide you with step-by-step instructions. By the end of this tutorial, you’ll be an expert in using this powerful function to make your data analysis a breeze!
What Are IF Then Formulas?
Google Sheets is equipped with various features that make our work easier, such as the SUMIF, IFNA, and IF CONTAINS functions. The IF Then function in Google Sheets is one of them. The IF formula in Google Sheets can be used to check a condition and return a specified value if it’s TRUE, or return a different specified value if it’s FALSE.
To put it simply, if you want to perform a logical test in a Google Sheets formula that returns different results based on the truth or falsehood of the test, you need to use the Google Sheets IF formula. You can use different formulas to make the function work the way you need it to. In this article, we’ll discuss how to use it in Google Sheets. So, let’s dive in!
How to Use IF Then in Google Sheets
Now that we understand what the IF Then formula does in Google Sheets, let’s look at the basic syntax of the function. It can be written as follows:
IF(logical_test, value_if_true, value_if_false)The logical_test argument in the syntax is usually the condition you’re checking in the function. It’s an expression or a reference to a cell containing an expression that would evaluate to a logical value, such as TRUE or FALSE.
An argument in the Google Sheets IF formula is value_if_true, which is the value returned by the function if the logical_test is TRUE.
Finally, value_if_false, an optional parameter, is the value returned by the function if the logical_expression is FALSE. If the user hasn’t specified the value_if_false argument and the checked condition wasn’t met, the result of the function will return FALSE.
Step-by-Step Guide: How to Use IF Then in Google Sheets
Let’s walk through an example of a car purchase decision table. It includes the buyer’s budget and the expected mileage of the vehicle. First, I’ll use the IF formula to determine if the price is “Within Budget” or “Out of Budget.”
Here’s how to use the IF function in Google Sheets:
- Go to the desired range of cells where you want to display the results.
- Type the equal sign (=) followed by IF(
- Select the cell reference you want to check and add the condition.
- Add a comma and write the value to be returned if the condition is TRUE in quotation marks.
- Add a comma and write the value to be returned if the condition is FALSE in quotation marks.
- Press Enter.
In the screenshot above, I used the logical expression B6<B2, which means if the value of cell B6 is less than the value of cell B2, then the “Value_if_true” should be displayed. Otherwise, “Value_if_false” should be shown.
We entered “Within Budget” as our Value_if_true and “Out of Budget” as our Value_if_false.
Nested IF Statements in Google Sheets
You can nest multiple IF statements in Google Sheets within the same formula to perform a longer and more complex logical test, now that you know how to create an IF function with a single statement. There are several ways to use an IF function in Google Sheets, including performing calculations in the value section.
Nesting AND/OR with IF Statements
Since the IF function performs logical tests with TRUE or FALSE results, other logical functions such as AND and OR can be nested within an IF formula. This allows you to perform a preliminary test with multiple criteria. It’s important to note that the AND function requires all test criteria to be met to display a TRUE result, while a TRUE result for the OR function requires only one of the test criteria to be correct.
Here’s how we would add a nested AND statement in our example above:
First, we want to use a combination of IF and AND to add a second parameter to the IF function to check, like this:
=IF(AND(Logical_Expression_1, Logical_Expression_2), "Value_if_True", "Value_if_False")In the screenshot above, I followed the same process to add an IF statement, except this time, I added an additional logical expression to check. Now, we’re also making sure that the mileage is less than 10,800 by using the logical expression C6<B3.
To use IF AND, simply replace these formulas:
=IF(AND(Argument_AND_1, Argument_AND_2), value_if_true, value_if_false)You can add as many AND arguments as you want or use IF OR:
=IF(OR(Argument_OR_1, Argument_OR_2), value_if_true, value_if_false)- Go to the desired range of cells where you want to display the results.
- Type the equal sign (=) followed by IF
- Type the AND function.
- Add the first condition. Then add a comma.
- Add the second condition. Since we’re looking for cars with lower mileage than our value, our condition is C6<B3.
- Close the parentheses and add a comma.
- Write the value to be returned if TRUE in quotation marks. Our value is “Suitable for Purchase”
- Add a comma and write the value to be returned if FALSE in quotation marks. Our value is “Unsuitable for Purchase.”
- Press Enter and drag the small circle in the bottom right of the cell to fill the corresponding cells.
With the formula above, we can find the difference if the car’s price is within the budget and these equations.
Given that the equation for TRUE and FALSE repeats in this case, the results will be negative if the car is over budget.
Using IF-THEN Statements with Calculation
We’ve shown you how to use the IF THEN formula in Google Sheets with text, but you can also use it with calculations. This means that if the conditions are met, the formula should return the results of a calculation. Let’s look at our spreadsheet example:
We can show the difference if the price exceeds the budget.
Here’s how:
- Go to the desired range of cells where you want to display the results.
- Type the equal sign (=) followed by IF
- Add the condition. Since we’re looking for cars below our budget, our condition is B6<B2
- Add a comma and write the equation for TRUE.
- Add a comma and write the equation for FALSE.
- Press Enter and drag the small circle in the bottom right of the cell to fill the remaining cells or use the autofill function.
With the formula above, we can find the difference if the car’s price is lower than the budget and these equations.
Nesting with Other IF Statements
While working with multiple IF statements in Google Sheets may seem complex, all you need to type is:
=IF(first_test, value_if_true, IF(second_test, value_if_true, value_if_false))You can nest as many IF statements as you want. Additionally, you can use a nested IF THEN statement in Google Sheets as the “value_if_true” argument in the same way. To do this, simply use the following syntax:
=IF(first_test, IF(second_test, value_if_true, value_if_false), value_if_false)It’s also worth noting that you can also use the IF function in conditional formatting. However, you can’t use the IF THEN ELSE syntax in a single conditional formatting rule. Only the IF THEN logic is used for conditional formatting. In order for conditional formatting to be applied, it must evaluate to TRUE.
Frequently Asked Questions
How do I write an IF THEN formula in Google Sheets?
- Open your Google Sheets spreadsheet, then type the syntax of the Google Sheets IF THEN formula, which is
=IF(logical_test, value_if_true, value_if_false)in a cell. - The logical_test is what you want to check in the statement.
- The first argument tells the function what to do if the comparison is true. The second argument tells the function what to do if the comparison is false.
- Read this article where I explain everything you need to know about how to create IF THEN statements in Google Sheets, which also applies to IF ELSE statements in Google Sheets.
How do I add multiple conditions in an IF statement in Google Sheets?
While it’s not possible to add multiple statements in an IF function, you can use the IFS function in Google Sheets to test many conditions at once and then return the result. The IFS function differs from the IF function in that it allows you to test multiple conditions at the same time. When all conditions are deemed TRUE, the corresponding value is returned.
Can I use an IF formula in conditional formatting in Google Sheets?
Certainly! Conditional formatting in Google Sheets allows you to change the appearance of a cell, such as the background color or text style, based on the rules you set. Each rule you create is an IF THEN expression. For example, you can say “If cell B2 is empty, change the background color of this cell to black.”
However, the IF THEN ELSE syntax cannot be used in a single conditional formatting rule. Only the IF THEN logic is used for conditional formatting. In order for conditional formatting to be applied, it must evaluate to TRUE.
What’s the difference between IF and IFS?
IFS in Google Sheets examines many conditions, while IF examines only one. I use the IFS function in Google Sheets when I want to add multiple conditional statements to my formula. The IF function facilitates a single condition.
If you’re looking to delve further into your data, I also have a video on using the INDEX MATCH function.
Conclusion
I’ve shown you how to use the IF THEN function in Google Sheets. I’ve included a detailed explanation of the syntax of the formulas, showing exactly what data to include to get a relevant result.
Now, you should know how to write an appropriate IF statement. If this guide doesn’t answer all the questions you have about the Google Sheets IF THEN formula, I recommend checking out the related content below.
Related:
- Using IF CONTAINS in Google Sheets
- How to Apply Conditional Formatting Based on Another Cell
- Google Sheets IFS Function Guide
- How to Use the Not Equal to Operator in Google Sheets
- Converting Formulas to Values in Google Sheets
