IF / IFERROR Function in Google Sheets
The IF function is used to test whether a cell or range of cells meets certain criteria in a logical test, and it always results in either TRUE or FALSE.
When you use the IF function, you create an imaginary decision tree in which a condition is followed by certain actions depending on whether the condition was met.
In simple words, if your condition is TRUE, the function does one thing. If it’s FALSE, the function does another thing.
You can define the condition and the outputs for TRUE and FALSE results with the arguments of the function.
What is the IF / IFERROR Function?
The syntax of the IF function is:
=IF(logical_expression, value_if_true, value_if_false)
The logical_expression argument is a logical question that can be answered with either yes or no (TRUE or FALSE). You can write a logical expression using such operators such as “=”, “>”, “<“, “>=”, “<=”, “<>”.
The value_if_true and value_if_false arguments are the values the function returns according to the result of the logical expression.
When to use IF / IFERROR for marketing stuff?
- Checking meta descriptions and title tags lengths.
- Check if URLs match.
- Sorting data with specific conditions
How To Use The IF / IFERROR Function in Google Sheets
Let’s say we have three columns of data:
- Total spend: how much each user spends in total
- Users: total number of individual users
- Spend per user: total spend divided by number of users
To work out how much each users spends, we divide the total spend by the users. The formula looks like this:
=A4/B4
Only probably is, there’s a zero in our numbers which is going to break our formula. Maybe it is a reporting error or maybe some imaginary users really did spend some money.
Either way, we can fix it by wrapping out formula in an IFERROR:
=IFERROR(A2/B2,"oops")
In this example, the error message is set to display “oops” when there’s an error. You can use any error message you want, or leave the space between the speech marks blank to leave the cells blank (if they error)
IF / IFERROR Examples
Let’s see an example with the following IF formula:
=IF(B2>=18,"Adult","Child")
You can use this function to categorize people according to their ages. The function tests the logical expression (greater than or equal to 18) and for TRUE results it outputs “Adult” while for FALSE results it outputs “Child”.
Since the IF functions are decision trees, you can always nest many IF functions within each other to split the results in more details.
For example, let’s add a third option to the above formula and return “Teenager” for those children who are between 11-18 years old.
To do this, you need to nest a second IF statement inside the first one.
=IF(B2>=18,"Adult",IF(B2<11,"Child","Teenager"))
The first logical expression tests whether the number is greater than or equal to 18. If it’s TRUE it returns “Adult”, if it’s FALSE then it goes to the second logical expression and based on whether that’s TRUE or FALSE returns “Child” or “Teenager”.
Using this method, you can keep nesting even more logical tests within each other and perform more complex tests.
A special variation of the IF function is the IFERROR function. It returns a specified result when a formula generates an error and a standard result when there is no error.
You can use it to catch errors and replace them with a blank cell, another value or your own text.
=IFERROR(value, [value_if_error])
The difference from the IF function is that you don’t have to define the logical expression. The condition it checks is whether the first argument, value generates an error. If it doesn’t result in an error, then the value itself will be returned. If it does generate an error, the second argument, value_if_error will be returned.
The second argument is optional and if you don’t define it, the function returns a blank cell in case of an error.
Suppose you’re calculating what percentage of your visitors made a purchase on your website. If there is a day with 0 visitors, your division results in an error that makes the sheet look bad.
You can use the IFERROR function to avoid such error messages from your sheets. You just wrap your formula in an IFERROR function and it takes care of
=IFERROR(B2/A2*100, "No visitors")
In this case, the value_if_error message (“No visitors”) will be returned in case the value expression results in an error.
Summary
IF you liked this tutorial (see what I did there) you might also like these: