If you’re looking to get started with a Formula Column for the first time, or if you’re on your way to becoming a Formula Column expert, the IF function is probably the place to go. With the IF function’s almost endless useful capabilities, it’s no surprise that it’s the most widely used function in a Formula Column!
In this article, we will first learn about the basics of the IF function and then explore variations and additional conditions that can be added to it. Keep reading to learn what the IF function is, how it works, and examples of how to use it.
Understanding the IF function
If you are new to the world of formulas, you might be trying to figure out “What is the IF function?” Well, basically, the IF function is a type of formula used to create a conditional statement, and it will return or display a value depending on whether the specified condition is met or not.
In its simplest form, an IF function formula is structured like this:
- First, the condition (for example:
IF({Status}="Done") - Then, the value returned if the condition is met (for example:
"YAY!"). - Finally, the value returned if the condition is not met (for example:
"לא עדיין..."))
When they are combined into one formula, it will look like this:
IF({Status}="Done","YAY!","Not yet...") Let’s break it down!
In the example shown above, the formula says that if the status is set to “Done”, then it will return the value “YAY!”. On the other hand, if the status is not set to “Done”, then the value “Not yet…” will be returned. Therefore, the result of this formula depends on whether the status is labeled “Done”.
This is what it will look like when it’s on a board! ⬇️
A simple example of using the IF function
Now that we understand what the IF function is, and how it is structured in its most basic form, let’s look at a budget tracking case study to see how the IF function can be used in a real-life example!
The table above is used to track an organization’s budget. Thanks to the Formula Column, we can see at a glance how the actual amount spent compares to the set budget. The following formula is used here:
IF({Budgeted}<{Actual Spending},"Over Budget","Under Budget") With this function in place, the formula column will read if the value of “Budget” is less than the value of “Actual Expenses”. If the value of “Budget” is indeed less than the value of “Actual Expenses”, then the text “Over Budget” will appear. Otherwise, if this condition is not met and the value of “Budget” is greater than “Actual Expenses”, then the text “Under Budget” will be displayed instead.
Using AND/OR conditions with the IF function
When using the IF function, it may be necessary to specify additional conditions. This will be especially important when the goal is to build longer and more complex calculations! Because of this, additional logical functions such as AND or OR can be combined with the IF function.
As shown above, when an additional condition or function is added to an IF formula, the additional statement (i.e.AND({Total Sales}>350000,{Deals}>12) ) will be enclosed within the initial IF statement. Let’s dive into another example of an AND condition, and then an example of an OR condition, below to see how it works!
AND condition
The AND function can be used within an IF formula to allow you to test multiple conditions to display a value. When used, all of the conditions specified must be met in order to display a value. If only one or none of these conditions are met, the value specified in the formula for the conditions that are not met will be displayed.
Let’s look at the Sales Lead Management dashboard below to see an example of how IF and AND functions can be combined to determine whether or not to receive a $2,500 bonus:
On this board, we added a formula column called “Bonus Value” to display the $2,500 bonus amount that a sales rep might be eligible to receive. For a sales rep to be eligible to receive the $2,500 bonus, the deal size must be over $350,000 and the payment status must be marked as “Paid.” The following formula would allow us to do just that:
IF(AND({Deal size}>350000,{Payment}="Paid"),"$2500","$0")
The example formula above would work like this:
- The formula will first check whether the transaction size is greater than 350,000 and the payment status is marked as “Paid”.
- If both of these conditions are met, the value of “$2500” will be returned.
- Otherwise, if any of these conditions are not met , then the formula will return the value of “$0”.
Great! Thanks to the combination of the AND and IF functions, our formula can easily help us determine whether a sales rep is eligible for a $2,500 bonus or not!
OR condition
Similar to AND conditions, the OR function also allows you to test multiple different conditions to return a value. The main difference between them is that for OR conditions, only one of the conditions must be true in order to return a value.
To see this in action, let’s take a look at the Sales Team Commissions table below! Similar to the previous usage, this table allows us to calculate the bonus rate for each of our reps. However, this time, the formula column (called “Bonus Rate”) takes into account additional variables when it performs the calculation.
This time, for a sales representative to be eligible for a bonus, one of the following two conditions must be met:
- Or its total sales amount must be greater than or equal to (> =) Sales target
- Or the number of its accounts must be greater than or equal to (> =) Accounts destination
IF(OR({Total sales}>={Sales goal},{# accounts}>={Account goal}),{Total sales}*{Commission rate},0) If one of the two conditions is met (sales including> = Sales target or number of accounts> = Account Target), then the representative will receive a bonus calculated by multiplying their total sales value by the commission rate. Otherwise, if none of these conditions are met, their bonus rate will be $0.
Cross-referenced IF formula
The final type of IF function that will be described here, known as a nested IF formula, works by combining multiple IF functions inside each other. This is used when you want to test for multiple different conditions and return a specific value depending on which of those conditions are met.
To understand how this might work, let’s look at the spreadsheet below that is used to manage a T-shirt manufacturing company! The formula column labeled “Price per Unit” will be the focus for this example.
Within this table, there are three different types of shirts indicated by the status column labeled ‘Shirt Type’: Ironman, Thor, and Captain America. Using a cross IF formula, we can easily and automatically display the unit price based on the selected shirt type!
Before we dive into this formula, it’s important to note that the prices of the different types of shirts are as follows:
- Thor: $15
- Ironman: $20
- Captain America: $30
To display the price per unit, based on the type of shirt (status label) selected, we want to include three different IF functions all placed inside one.
To create a formula that expresses this logic, we will first check if the shirt type is “Thor”, if so, the value displayed will be 15 (depending on its cost). If this condition is not met, we will continue to the next IF statement where we will check if the shirt type is “Ironman”, and if so, the result will be 20. For the final option, we will check if the shirt type is “Captain America” and if so, that will give a value of 30. If none of these conditions are met, then 0 will be displayed.
The resulting formula will look like this:
IF({T-shirt Type}="Thor",15,IF({T-shirt Type}="Ironman",20,IF({T-shirt Type}="Captain America",30,0))) Notice how each IF function has its own parentheses that open each statement separately, and then all close together at the end? This is how a cross-IF function should be constructed for it to work properly.
With this formula built in, the price per unit will automatically be displayed in response to selecting the shirt type!
Awesome! We hope this article has given you the foundation to start exploring the IF function in a formula column. There are so many calculations you can create with it, and you can get really creative here!