Blog

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.