Excel If Then Statements: Getting the Most Out of the IF Function

Excel is often used as a simple spreadsheet application to keep track of facts and figures, add numbers together, or do other straightforward arithmetic functions. But Excel has grown from its simple spreadsheet days to a complex program that allows you to embed programming in the spreadsheet itself. The programming functions in Excel can make it a lot easier to analyze the data within the spreadsheet or create generic calculations that handle differing inputs.

The IF function in Excel is a good example of a simple function that can make your life easier and help you perform more advanced calculations and conditional formatting.

I wrote this tutorial with the beginner in mind, but to understand and follow the tutorial, you need to know how to add data to cells and perform basic functions with Excel spreadsheets. 

Understanding the IF function

The syntax of the IF statement in Excel is as follows:

IF(condition, [value_if_true], [value_if_false] )

The condition must be a value or a logical function that evaluates to either TRUE or FALSE.  Reading it as an IF-THEN statement can help you understand how to use this function. If the condition is true, then the value in the second parameter will appear in the cell. If it does not meet the condition, the third parameter you used in the function will be displayed.

A common conditional used in IF statements are expressions to check the relationship between two values in your spreadsheet. For example, A1 > B1 is a logical expression that checks if the value in cell A1 is greater than the value in cell B1. If the values were 6 and 3, then 6 > 3 would evaluate to TRUE.

You can use any of these comparison operators in the condition statement:

• =     equal to
• >     greater than
• <     less than
• >=  greater than or equal to
• <=  less than or equal to
• <>  not equal to

Let’s look at a simple example.

IF( x > 3, x * 3, x / 3 )

Here we can read this statement as:

If x is greater than 3 is TRUE, then return the value x times 3, else if x greater than 3 is FALSE, then return the value x divided by 3.

So, what the value x is 6, what is the result?

IF( 6 > 3, 6 * 3, 6 / 3 ) = 6 * 3 = 18

Here, since x is greater than 3, the condition evaluates to TRUE, and the IF formula returns the value_if_true, which in this case is x * 3 or 6 * 3 = 18.

Now, if x is 1, what is the result?

IF( 1 > 3, 1 * 3, 1 / 3 ) = 1 / 3 = 0.333333333

Here, since x is not greater than 3, the condition evaluates to FALSE, and the IF formula returns the value_if_false, which in this case is x / 3 or 1 / 3 = 0.333333333.

In the simple examples above, we used the IF function to return a calculated numeric value. However, you can use the IF function for so much more.

Three examples of using the IF function

Segmenting datasets using the IF function

A simple and frequent use case for the IF function is to identify subsets of a given dataset that meet criteria and label that data.

For example, if we have a list of student scores on a pass or fail exam where scores < 70 is a FAIL and scores >= 70 is a PASS, we can use an IF function like this:

IF( SCORE < 70, "FAIL", "PASS")

Notice, the value_if_true and value_if_false are in quotations. If we want the results to be text, then we must enclose the values in quotation marks. Using that formula above in a spreadsheet, we can let Excel label all the grades in a spreadsheet as PASS or FAIL instead of figuring it out from the scores ourselves.

Nesting IF functions for categorizing data

This example is similar to our last example, but while our last example had two labels, this one will have more. Let’s say you want to categorize the stores of a multi-unit restaurant chain based on their sales growth, where:

• Store sales growth of < 0% (meaning negative sales growth) gets a LOW PERFORMER label.
• Store sales growth of 0 – 5% gets an AVG PERFORMER label.
• Store sales growth of >5% gets a HIGH PERFORMER label.

If we had data that looked like this, what would the formula in the Category column look like?

STORE	SALES_GROWTH	PERFORMANCE
Store 1	-5%
Store 2	4%
Store 3	6%

Well, we could do something like this.

IF( SALES_GROWTH < 0, "LOW PERFORMER", IF(SALES_GROWTH < 5, "AVG PERFORMER", "HIGH PERFORMER"))

Notice the nested IF function inside the first IF function. The second IF function is the value_if_false for the first IF function.

When we’re doing simple data categorization with a small number of categories, using nested IF functions is an easy solution. The number of IF functions you need will be n – 1, where n is the number of labels you are using.

Using other functions in the condition

We’ve used the IF function inside of another IF function. You can also use other functions inside the IF function. Here are some useful examples.

What do you think this does?

IF(ISBLANK(A1), 0, A1)

In Excel, there is a class of IS functions that allow you to check various attributes of a cell. ISBLANK above is checking whether the cell, A1, is blank. If A1 is blank, then the expression evaluates to true. Other popular IS functions include ISNUMBER, ISERROR, and ISTEXT. Using these functions with the IF function can help you check the attributes of a cell efficiently.

What do you think these do?

IF(AND(A1 >= 9, A1 <= 12), "HS Student", " ")
IF(OR(A1 = "Toyota", A1 = "Honda", A1 = "Mitsubishi", "Japanese Car", " ")

In Excel, there are also logical operator formulas we can use with the IF function to allow us to check multiple conditions simultaneously. 

The AND function used in the first example requires that all of the conditions inside the AND function be true for the entire expression to evaluate to true. Here, A1 must be between 9 and 12, inclusive, which in this case represents the student’s grade level.

The OR function in the second example requires only one of the conditions inside it to be true for the entire expression to evaluate to true. Here, we are labeling car manufacturers who are from Japan.

What else can you use IF functions for?

The examples above are only some of the things you can use the IF function for. In combination with other functions and techniques, you can use the IF function to help answer questions based on your data set. Some examples are:

• What is the nth largest/smallest number in a list?
• Which weekdays get the most rainfall?
• Is my data valid based on certain conditions?

As you become more familiar with Excel and the IF function, you’ll discover new uses specific to your domain and needs.