Google Sheets IF ELSE

The IF ELSE function in Google Sheets is a powerful tool for making decisions based on specific conditions within your data. While the basic IF function allows you to specify one action for a "TRUE" condition and another for a "FALSE" condition, IF ELSE introduces more flexibility by enabling multiple conditional checks. This allows you to create more complex logic and automate your data analysis. In this guide, we will explain how to use the IF ELSE structure in Google Sheets, covering both basic and advanced examples to help you understand how to apply this function in your spreadsheets.

IF ELSE in Google Sheets

In Google Sheets, there isn't a direct "IF ELSE" function, but you can achieve the same result by using the IF function combined with its "value_if_false" argument. This allows you to set an alternative outcome if the condition is not met, mimicking the behavior of an "else" statement in programming.

How IF ELSE Works:

• IF: Checks a condition.
• ELSE: Defines what to do if the condition is false.

Google Sheets IF Else Formula

In Google Sheets, the IF ELSE functionality is achieved using the IF function. The ELSE part corresponds to the value returned when the condition is false.

Syntax:

=IF(condition, value_if_true, value_if_false)

Explanation:

• condition: This is the logical test that will be evaluated. It can be anything that results in either a TRUE or FALSE outcome.
• value_if_true: This is the value that the formula will return if the condition is TRUE (or met).
• value_if_false: This is the value that the formula will return if the condition is FALSE (or not met).

How to Use the Google Sheets IF ELSE Function

To mimic an "IF ELSE" statement in Google Sheets, you'll use the IF function to define the condition and provide two possible outcomes. Here is how you can use Google Sheets IF ELSE formula:

Step 1: Open Google Sheets and Select a Cell

• Open your Google Sheets document, or create a new one.
• Click on the cell where you want the result of the IF ELSE statement to appear (e.g., B2).
• This is where the outcome of your logic will be displayed.

Step 2: Write the IF Formula

Start by typing the following formula:

=IF(condition,

Specify the condition you want to evaluate. For example, if you want to check if the value in B2 is greater than 50, the formula should start like this:

=IF(B2>50,

Step 3: Define the "True" Value

After the comma, define the value that should appear if the condition is true. For example, if the condition is true (i.e., B2 > 50), you might want to display "Above 50".

=IF(B2>50, "Above 50",

Step 4: Define the "False" Value

After the second comma, define the value to display if the condition is false (i.e., if B2 is not greater than 50).

For example, you can display "Below or Equal to 50" if the condition is false.

Your final formula will look like this:

=IF(B2>50, "Above 50", "Below or Equal to 50")

Step 5: Press Enter

Press Enter to apply the formula.

The result of your condition will now appear in the selected cell.

• If B2 is greater than 50, the cell will display "Above 50".
• If B2 is less than or equal to 50, the cell will display "Below or Equal to 50".

Step 6: Apply the Formula to Other Rows:

• After typing the formula in C2, click the small square at the bottom-right corner of the cell.
• Drag it down to fill the rest of the cells in Column B.
• Google Sheets will adjust the formula for each row (e.g., B3, B4, etc.).

Preview Result:

Your sheet will look like this after applying the formula:

This logic helps you perform a quick decision-making process based on the condition you define. The IF ELSE formula is useful for categorizing or providing dynamic results based on your data in Google Sheets.

IF ELSE Google Sheets Examples

Here we will understand IF ELSE in Google Sheets with different cases:

Using a Cell Reference for the ELSE Statement

In Google Sheets, you can store the ELSE value (the result for when the condition is false) in another cell. This makes it easier to manage and update the outcome of the IF formula without needing to modify the formula itself. This approach is particularly useful when you want to keep your formulas clean and flexible, and when the outcome (like "Pass" or "Fail") might change.

Example Scenario:

Imagine you have a list of students and their exam scores. You want to check if a student's score is greater than or equal to 60 (passing score). If the score is above 60, you want to return "Pass", and if it is below 60, you want to return the value from a different cell that indicates "Needs Improvement".

Step 1: Set Up Your Data

Let's assume the following data in Google Sheets:

Step 2: Write the Formula with Cell Reference in ELSE Statement

In C2 (under the "Status" column), type the following IF formula to check if the value in B2 (Score) is greater than or equal to 60:

=IF(B2>=60, "Pass", D2)

• This formula checks if the score in B2 is greater than or equal to 60.
• If true (the score is above or equal to 60), it returns "Pass".
• If false (the score is below 60), it returns the value in D2 (which is "Needs Improvement" in this case).

Step 3: Press Enter

• After typing the formula, hit Enter.
• The formula will check if the score in B2 is greater than or equal to 60. If it is, it will return "Pass". If not, it will return the value from D2.

Step 4: Apply to Other Rows

To apply the formula to the rest of the rows, drag the formula down:

• Click the small square handle at the bottom-right corner of C2.
• Drag the handle down to C5 (or however many rows you need).

Google Sheets will automatically adjust the formula for each row, checking each score and returning the corresponding result from column D when the condition is false.

Preview Result:

Your sheet will look like this after applying the formula:

Explanation:

• For John: Since B2 = 75 (which is greater than or equal to 60), the formula returns "Pass".
• For Alice: Since B3 = 55 (which is less than 60), the formula returns "Needs Improvement" from D3.
• For Bob: Since B4 = 90 (which is greater than or equal to 60), the formula returns "Pass".
• For Sarah: Since B5 = 45 (which is less than 60), the formula returns "Needs Improvement" from D5.

This example shows how you can use a cell reference for the ELSE statement in an IF function. Instead of hardcoding a result like "Fail" or "Needs Improvement", you can reference another cell (in this case, D2) to pull in data dynamically, making the formula more flexible.

Using Nested IF for Multiple Conditions

Here's a step-by-step guide on how to use Nested IF statements in Google Sheets to handle multiple conditions.

Example Scenario:

Imagine you are grading students based on their scores. You want to categorize the students into different grade categories:

• "A" for scores above 90
• "B" for scores between 70 and 90
• "C" for scores between 50 and 70
• "D" for scores below 50

Step 1: Set Up Your Data

Let’s assume you have the following data:

Step 2: Write the Nested IF Formula

You will use a nested IF formula to assign grades based on the score.

In C2, type the following formula:

=IF(B2>90, "A", IF(B2>70, "B", IF(B2>50, "C", "D")))

This formula works as follows:

• The first IF checks if the score in B2 is greater than 90. If true, it returns "A".
• If the first condition is false, it moves to the second IF to check if the score is greater than 70. If true, it returns "B".
• If both previous conditions are false, the third IF checks if the score is greater than 50. If true, it returns "C".
• If all conditions fail (i.e., the score is 50 or below), it returns "D".

Step 3: Press Enter

• After typing the formula, press Enter.
• The formula will evaluate the score in B2 and return the corresponding grade based on the conditions defined in the nested IF statements.

Step 4: Apply the Formula to Other Rows

To apply the formula to the remaining rows:

• Click the small square handle at the bottom-right corner of C2 (the cell with the formula).
• Drag the handle down to C5 (or however many rows you need).
• Google Sheets will automatically adjust the formula for each row, checking the corresponding score in column B and returning the appropriate grade.

Preview Result:

After applying the formula, your sheet will look like this:

Explanation:

• John: His score is 95, which is greater than 90, so the formula returns "A".
• Alice: Her score is 85, which is greater than 70 but less than 90, so the formula returns "B".
• Bob: His score is 65, which is greater than 50 but less than 70, so the formula returns "C".
• Sarah: Her score is 45, which is less than 50, so the formula returns "D".

This nested IF function checks multiple conditions in a hierarchical manner, allowing you to handle more complex decision-making within a single formula. Nested IFs are especially useful when you have more than two conditions to evaluate and need to return different results based on each condition.

Notes:

• Nested IF functions can become complex, so it’s important to ensure that each IF is correctly closed with a parenthesis to avoid errors.
• If you have many conditions, using a switch or case function (like IFS() in Google Sheets) might make your formula easier to read and manage.

Also Read:

• How to Password Protect a Google Sheet
• Types of Trendlines in Google Sheet

Conclusion

The Google Sheets IF ELSE function is an essential tool for decision-making in spreadsheets. By combining the IF function with the ELSE statement, you can evaluate conditions and return different results based on whether those conditions are met. Whether you are working with simple comparisons or more complex logical tests, the IF ELSE function allows for greater automation and flexibility in data analysis. It's especially useful for categorizing data, performing calculations, and creating dynamic reports. With its easy-to-use syntax, the IF ELSE function enhances your ability to manage and analyze data efficiently in Google Sheets.