The IF ELSE function in Google Sheets can be used to set conditions that give an output depending on whether a given condition is fulfilled or not. It works as follows:
If condition is true, then value if true; else, value if false
The IF function can work without the ELSE value but it will display the value FALSE if the condition is not fulfilled. The same logic applies to the IFS function.
While there is no explicit ELSE function in Google Sheets, it does not mean that you cannot add the ELSE value to your IF function. In fact, the ELSE value is within the syntax of the IF function!
Read this guide to learn how to replace the FALSE value with something else and ensure your sheet looks neater than before.
IF ELSE Google Sheets Syntax
ELSE as value
=IF(condition, value_if_true, else_value)
Where
condition is the condition to fulfill
value_if_true is the value displayed if the condition is met
else_value is the value displayed if the condition is not fulfilled
ELSE IF statement
=IF(condition1, value_if_true1, IF(condition2, value_if_true2, value_if_false))
Where
condition1 is the first condition to fulfill
value_if_true1 is the value displayed if condition1 is met
condition2 is the condition to check if condition1 is not met
value_if_true2 is the value displayed if condition2 is met but not condition1
value_if_false is the value displayed if both condition1 and condition2 are not met
IF ELSE Google Sheets Examples
ELSE statement as string
Step 1: Identify the condition and the values to display
We want to know whether the entry passed or failed the assessment by checking if the weighted score is at least 37. The resulting conditions and values are the following:
Condition: weighted score > 36
Value_if_true: “PASSED”
Value_if_false: “FAILED”
Step 2: Use =IF(condition, value_if_true, value_if_false)
The function is now
=if(E2>36,"PASSED","FAILED")
Step 3: Press Enter
You can opt to extend the function to the other rows of the column, giving you this result:
ELSE statement stored in another cell
Step 1: Identify the condition and the values to display
We want to know whether the entry passed or failed the assessment by checking if the weighted score is at least 37. For this example, we will store the values intoThe resulting conditions and values are the following:
Condition: weighted score >= 37
Value_if_true: “PASSED”
Value_if_false: “FAILED”
For this example, we will store these values to other cells, and then point to them.
Step 2: Use =IF(condition, value_if_true, value_if_false)
Noting where the values are stored, the function for Row 2 is now
=if(E2>=$G$2,$G$5,$G$6)
We added the dollar signs since we plan to copy this to other rows in the same column.
Step 3: Press Enter
You can opt to extend this to the other rows of the column, giving you this result:
ELSE IF statement
You can set another IF statement as the ELSE value, converting it to ELSE IF.
Step 1: Identify the conditions and the values to display
At this time, we want to introduce a tiered assessment system. First, we want to check whether the entry passed or failed the assessment by checking if the weighted score is at least 37:
Condition1: weighted score > 36
Value_if_true1: “PASSED”
Next, we want to check if the score difference is less than 5:
Condition2: 36 - weighted score < 5
Value_if_true2: “CLOSE TO PASSING”
Value_if_false: “FAILED”
Step 2: Use =IF(condition, value_if_true, value_if_false)
The function is now:
=if(E2>36,"PASSED",if(36-E2<5,"CLOSE TO PASSING","FAILED"))
Step 3: Press Enter
You can opt to extend this to the other rows of the column, giving you this result:
Sample Sheet
Check out our IF ELSE Google Sheets sample sheet here.
Supercharge Your Spreadsheets with Lido
Related IF Functions
IF: Allows you to check for specific conditions across a dataset
SUMIF: Use to sum numbers if they meet a certain condition..
SUMIFS: Sums data from cells that meet multiple criteria
COUNTIF: count data if it fulfils certain criteria
COUNTIFS: Count data that fulfils two or more criteria.
COUNT IF Not Null: Count cells if they contain data
IFERROR: Allows you to output values when an Error in a formula occurs
IFTHEN: Allows you tor write statements that use IF X Then Y Logic
IF AND: Combines the functionality of the IF and AND functions
Multiple IF: Learn how to use multiple if statements in a single formula
IF OR: Evaluates multiple conditions and produces a TRUE result when any of them are met.
IF Contains: Returns cells that contain a particular text.
AVERAGEIF: Calculate an average for numbers within a data range if they meet the provided criteria.
Recommended articles
More resources
