Google Sheets IF() Function with Examples
To evaluate whether a certain condition is TRUE or FALSE in Google Sheets, you can use an IF() or IFS() function. With IF() function, you can include other functions or nest multiple layers of IF() function in more complex situations. This post provides examples and comparisons of data types when using an IF formula.
You can make a copy of the Google Sheet here
Table of Contents
Google Sheets IF Function Syntax, Arguments / Parameters
IF() function has the following syntax:
=IF(test, value_if_true, value_if_false)
Note that you must put in the round brackets as shown above, or the functions will result in error.
For “test“, you need to be familiar with the usage of comparison operators, and know which data type you are comparing with, in order to provide a valid operation to perform the test.
“value_if_true” is the value to use or function to carry out if the condition is true.
“value_if_false” is the value to use or function to carry out if the condition is false.
If a test results in TRUE, Google Sheets will skip value_if_false all together.
For both “value_if_true” and “value_if_false”, you can replace these with an actual value, a cell address containing a value, or a function that results in a value.
IF Function Examples For Different Data Types
As shown above, you need to
- Use logical operators for comparison of numerical, date or text values
- Use Exact() function for comparison of case-sensitve words
If you change to “Comparing with 0” tab and “Comparing with blanks” tab, you will see the formulas to use in these situations. In brief, to test a cell say cell A1 for 0, use
=A1=0
and to test say a cell A1 for blanks, use
=A1=””
where double quotation marks represent blanks.
Nested IF Function in Google Sheets
Nested IF() functions follow the same syntax as IF() function described above. You can nest multiple IF() functions. Here we will see the mechanism behind one layer of nested IF() function.
IF( test_1, IF( test_2, value_if_true, value_if_false_2), value_if_false_1)
This one tests for two possible outcomes if the first test turns out to be TRUE.
For example,
The formula
=IF(A1=”Country”, IF(A2=”Britain”, “UK English”, “US English”), “”)
tests to see if the value if cell A1 is “Country”. If it is, the formula further checks if cell B2 is “Britain”, and if so, show “UK English”, otherwise no matter the value in cell B2, it defaults to show “US English”. If cell A1 is not “Country” at all, then show as blank.
It can quickly become confusing when you try to nest more than two IF() statements. As such, it is better to draw out the decision tree to help you construct the formula.
You do not need any fancy flow chart app. Doing it on Google Sheets will be enough.
IF( test_1, value_if_true_1, IF( test_2, value_if_true_2, value_if_false))
This one tests for two possible outcomes if the first test turns out to be FALSE.
Here, value_if_true_2 will not be triggered if value_if_true_1 is triggered.
IF() Function with AND() and OR()
IF() Function is frequently used with AND() and OR() function to test more complex conditions. This is the subject addressed in the post on IFS() function, which is used when you feel the urge to put multiple IF() together.
IFS Function
IFS() function is more flexible in the way that it allows you to provide multiple tests and what to do with each. This is useful when you have a tiered system such that you want to assign a value to each tier or grade. For example, you might assign academic results greater than 85 as “Distinction”, 65 as “Credit”, 50 as “Pass” and so on.
In general if you have more than two conditions to test for, or when you need to nest more than one IF() function, then consider using IFS() function instead.
Otherwise IF() function is flexible and tiny enough to be your everyday Swiss knife.
Check out this post: Google Sheets IFS Function with Detailed Examples
