Are you looking to make data-driven decisions in Google Sheets? Look no further than the IF function! This powerful tool is part of Google Sheets’ logical functions family and allows you to take actions based on specific conditions.
What is the IF function?
At the core of the IF function lies a test that evaluates a true or false value. Depending on the result, it executes a defined behavior if true and a different behavior if false.
Let’s take an example. Suppose you have two columns of numbers in A and B and want to compare each row to see which column contains the highest number. This is where the IF function shines!
Within the IF formula, the first expression A2 > B2 checks if the value in cell A2 is greater than the value in cell B2. The result of this test is either true or false.
The IF function requires a true or false value for its first argument. Next, you specify what you want to display when the result is true. In this example, the function outputs “Column 1 is larger” in the cell.
The last argument is the value to display if the result is false. In this case, it means that the number in column A was smaller (or equal) to the value in column B. In this case, the function outputs “Column 1 is smaller or equal to column 2”.
Syntax of the IF function in Google Sheets
The IF function can be referred to as IF, IF formula, or even IF statement; they all mean the same thing. It takes three arguments:
logical_expression
An expression that yields a true or false value, or a cell containing a true or false value.
value_if_true
The value to be displayed by the IF function if the logical expression is true.
value_if_false
The value to be displayed by the IF function if the logical expression is false.
Using the IF function for calculations in Google Sheets
IF functions can be combined with other functions to perform calculations on values above a certain threshold, for example.
When the logical expression is true, perform a specific calculation; otherwise, leave the value as is.
Perhaps your company has a performance bonus structure that pays a 20% bonus above a certain customer revenue threshold. Use an IF statement to determine if the threshold has been reached and then place the calculation in the true field:
Using the IF formula for classification
Another example of using the IF function is classifying items.
For instance, you may want to segment your customers into long-term customers and new customers based on their lifetime as customers (12 months or more).
Assuming column A contains the number of months a customer has been with you, the following IF formula would categorize them as long-term customers or new customers:
This type of segmentation is useful in various ways. It allows you to compare retention and churn metrics for both groups. You can run different marketing campaigns targeting different segments of your data. Or maybe you just want to send a thank-you card to your long-term customers, letting them know they’re awesome!
Nested IF formulas in Google Sheets
Sometimes, a single IF function is not enough. You can nest another IF function within the true or false arguments.
This may sound complex, but if you apply the layering method to work with formulas and work from the outside in, you’ll find it’s not difficult.
Starting from the outside, we check if the value is greater than 50. If it is, the formula displays “Greater than 50”.
But if it’s not, that means the value is less than or equal to 50, so we use a second nested IF formula to check that:
If your nested IF formulas become too complex, you may want to consider using the SWITCH function.
IF formulas and checkboxes
Combining checkboxes with IF formulas is a powerful and versatile technique to use in your spreadsheets.
Checkboxes are essentially disguised true or false values!
With a checkbox in cell A2, it can only have a true or false value. So, it can be directly integrated as the first argument of the IF function!
This kind of logic is useful in many contexts, such as on a dashboard where you want to show/hide additional information for specific figures or charts.
Direct entry of true or false
In the above example, you already had a formula in column A that contained a true or false value.
Since the first argument of the IF statement is looking for a true or false value, you simply reference that cell directly without trying to test true/false.
Your IF formula would look like this:
=IF(A2, "Good job!", "")
There is NO need to test true or false again. So, you should not see an IF function like this:
=IF(A2 = TRUE, "Good job!", "")
In fact, you should never see “= TRUE” or “= FALSE” in an IF statement, as it is redundant.
Replace numeric IF formulas with MIN or MAX functions
Suppose you have a column of values that you want to cap at a certain level, so anything above a threshold value, say 200, is set to that value.
Most of us would approach this by writing an IF formula that checks if the value is greater than 200 and sets it to 200 if true or the actual value if false, like this:
=IF(A1 > 200, 200, A1)
However, you can replace the IF function with a more concise MIN function, which chooses 200 if the actual value is greater, as 200 represents the minimum:
=MIN(A1, 200)
Similarly, you can use the MAX function to replace IF statements when looking for a threshold on the lower side.
It’s good practice to write efficient formulas as they are faster and less prone to errors.
