This article will start by covering the fundamentals of the IF function and later on, we will also examine other modifications and circumstances that can be incorporated into it. Continue reading to understand what the IF function is, how it functions, and to see some illustrations of how it can be utilized.

Understanding the IF function

For those who are new to the world of formulas, you might be wondering about the IF function. Essentially, the IF function is a type of formula used to create a conditional statement. Whether or not the set condition is met, it will return a certain value or output.

 

The basic structure of an IF function formula is as follows:

  • Firstly, the condition is stated (e.g., IF({Status}=”Done”))
  • Secondly, the value that is returned if the condition is met is specified (e.g., “YAY!”)
  • Finally, the value that is returned if the condition is not met is specified (e.g., “Not yet…”)
 

When combined into a single formula, it will look something like this:

 

Formula: IF({Status}="Done","YAY!","Not yet...")

Let’s break it down!

The formula depicted above will display “YAY!” if the status is set to “Done”, otherwise it will display “Not yet…”. In other words, the formula output depends on whether the status column is marked as “Done” or not. The following image shows how the formula will appear when implemented on a board:

To write a formula in a correct syntax, using parentheses to enclose any logical statements, including the IF statement, is essential. To illustrate this in the article, we will use colored parentheses in all formula examples.

Simple IF function use-case

Now that we have a basic understanding of the IF function and its workings, let’s explore a practical example of how it can be applied in real-life situations, such as budget tracking. 

The board displayed is designed for monitoring the financial plan of a company. The Formula Column allows us to quickly assess the difference between the actual spending and the budgeted amount. The formula utilized here is as follows:

Formula: IF({Budgeted}<{Actual Spending},"Over Budget","Under Budget")

Using the IF function in the Formula Column, the board will check if the “Budgeted” value is smaller than the “Actual Spending” value. If this condition is true, it will display “Over Budget”. However, if the condition is false and the “Budgeted” value is greater than the “Actual Spending” value, it will show the message “Under Budget”.

Using AND/OR conditions with the IF Function

It may be necessary to specify additional conditions to create more complex calculations using the IF Function. In such cases, logical functions such as AND or OR can be combined with the IF Function.

The above example demonstrates that when you add an extra condition or function to an IF formula, you place the additional statement (e.g., AND({Total Sales}>350000,{Deals}>12)) within the original IF statement. Below, we’ll look at an example of an AND condition and an example of an OR condition to see how this works.

AND condition

Combining the IF and AND functions allows you to test for several conditions to display a value. All the conditions must be satisfied for the value to be shown. If one or none of these conditions are satisfied, the value specified in the formula for unsatisfied conditions will be displayed.

The Sales Lead Management board is an excellent example of how to use the IF and AND functions to determine if a $2,500 bonus is warranted.

To determine whether a sales rep is eligible for a bonus of $2,500 on this board, we have created a formula column called “Bonus value.” To be eligible, the deal size must be greater than $350,000 and the payment status must be “Paid.” The formula used to determine this is shown below.

Formula: IF(AND({Deal size}>350000,{Payment}="Paid"),"$2500","$0")

The formula shown above functions in the following manner:

First, it checks if both the “Deal size” and “Payment status” columns meet the criteria of being over 350000 and “Paid,” respectively. If both conditions are met, it will return a “$2500” value. On the other hand, if either of these conditions is not satisfied, the formula will return “$0”. Thus, by using the combination of the IF and AND functions, this formula can assist in determining whether a Sales Rep is eligible for a $2,500 bonus.

OR condition

The OR function is similar to the AND condition in that it allows for testing multiple conditions to display a value. However, the difference is that with OR, only one of the conditions needs to be met for a value to be displayed.

Let’s look at the Sales Team Commissions board below to demonstrate this. Here, we can calculate a bonus rate for each sales representative, but the formula column titled “Bonus rate” takes into account additional variables for the calculation.

To qualify for a bonus, the sales rep needs to fulfill one of the two conditions – their Total Sales value must be greater than or equal to (>=) the Sales Goal, or their number of accounts must be greater than or equal to (>=) the Account Goal. The bonus will be calculated by multiplying their Total Sales value by the Commission rate. If both of these conditions are met, their bonus rate will be $0. The formula to perform this calculation will look like this:

Formula: IF(OR({Total sales}>={Sales goal},{# accounts}>={Account goal}),{Total sales}*{Commission rate},0)
 

If the Total Sales are greater than or equal to the Sales goal or the number of accounts is greater than or equal to the Account goal, then the sales rep will be eligible for a bonus. To calculate the bonus amount multiply the Total Sales value by the Commission rate. However, if neither of these conditions is met, the bonus rate will be zero.

Nested IF formula

The “Nested” IF formula is the third type of IF function we will discuss. It involves including multiple IF functions inside one another to test for different conditions and display specific values based on which conditions are met.

To illustrate this, we can take the example of a board that is used to manage a T-shirt manufacturing company. In this board, there is a Formula Column called “Price per unit,” which we will use for this particular use case.

By utilizing a nested IF formula, it’s possible to automatically display the price per unit depending on the chosen T-shirt type, as indicated by the Status Column labeled ‘T-shirt Type’ within this board. The T-shirt types available are Ironman, Thor, and Captain America.

It’s important to note that the different T-shirt types are priced as follows:

  • Thor: $15
  • Ironman: $20 
  • Captain America: $30

We need to use three nested IF functions to display the price per unit for a T-shirt type (status label). The first function checks if the T-shirt type is “Thor” and displays a value of 15, which is the cost for that type. If the first condition is not met, the formula moves to the next IF statement, which checks if the T-shirt type is “Ironman”. If the T-shirt type is “Ironman”, the formula will display a value of 20. The third and final option checks if the T-shirt type is “Captain America” and will display a value of 30 if it is. If none of the conditions are met, the formula will display 0. It’s important to note that each IF function must have its own set of separate parentheses that open and close.

The resulting formula: 

 Formula: IF({T-shirt Type}="Thor",15,IF({T-shirt Type}="Ironman",20,IF({T-shirt Type}="Captain America",30,0)))
 

The structure of a nested IF function requires each statement to have its own set of parentheses that open and close together in the end. This ensures that the function works correctly. Setting up this formula will automatically display the price per unit when a T-shirt type is selected.

Fantastic! We trust this article has furnished you with the necessary groundwork to explore the IF function in the Formula Column. With so many calculations you can generate with this feature, the possibilities for creativity are endless!

Note: To obtain more advice, techniques, and general knowledge on operating the Formula Column, look at this article. You can also discover more formula illustrations in our Formula Use Cases article. 
 

For further questions, please visit our Knowledge Center.  To view the Knowledge Center, click the Home Page icon the top right of your page, then select “Support” and make you way to “Help Center” in the bottom left.  If you have additional questions, please feel free to contact our team by selecting the blue “Contact Us” button.