If you’re familiar with programming, you know that the IF…Then function is a fundamental operation in every language. You can use it in Excel as well, where it acts as a logical function allowing you to check if a certain condition is true or false. It simply tells Excel that if a condition is true, perform action A, otherwise, perform action B.
In this tutorial, we will learn about the Excel IF statement, its syntax, applications, and cover some tips and tricks. For a more in-depth explanation of the IF function, take a look at this course on advanced concepts for Excel.
Understanding the Excel IF Function
The IF function is a logical function. It checks if a certain condition is true or false and then acts accordingly. For example, if a student has scored more than 75 points in a test, print “passed”, if not, print “Failed”. Most common uses:
Comparing values (is A1 greater then B1?)
Using calculations based on the result of the comparing values (if A1 is greater than B1, multiply A1 by 10)
If function has the following syntax:
IF(logical_test, value_if_true, value_if_false)
logical_test = the condition you are testing, for example: Is your name John? Did you score more than 75 points?
value_if_true = text, numeric value or formula if the condition is true
value_if_false = text, numeric value or formula if the condition is false
To test whether a condition is true or not, you can use logical operators. Excel supports the following logical operators:
< Less than
> Greater than
<= Less than or equal to
>= Greater than or equal to
= Equal to
<> Not equal to
Let’s understand this using an example.
Suppose you have the results from a test where a score above 75 is classified as “passed”, less than 75 classified as “failed”. You can do this easily by using an Excel IF function:
=IF(A1<75, “Failed”, “Passed”)
It’s sometimes easier to understand the IF function by putting it into words. In the above example, we are essentially saying: if the value in cell A2 is less than 75, then type “Failed” into C2. If the value is above 75, type “Passed” into C2.
Here’s another example. Let’s say you want to check if a user’s name is John:
=IF(A1=”John”, “Your name is John”, “Your name is not John”)
Let’s look at one more example where we use the IF function to find empty cells:
=IF(A1=””, “Empty”, “”)
So, using the IF function this way, we ask: Is the cell A1 empty? If it is, then write “Empty” and if it isn’t, don’t write anything, leave the cell blank.
Want to fast track your Excel success? This course will help you master Excel in hours, not week.s
IF Function and Formulas
Using IF function to make simple classifications is all fine, but to really utilize the power of the IF function, we have to use it with a formula.
Let’s consider an example.
Let’s say your budget for one year is $6000 and you have expenses entered by month. You want to know if you’ve exceeded your budget in any given year. To do this, you can use the SUM function inside the IF function.
Here’s the formula:
=IF(SUM(A2:L2)>6000,”Budget Exceeded”, “Budget OK”)
This formula basically adds the expenses of all the given months and checks if it meets the given condition (i.e. sum > 6000). If the condition is true, it says “Budget Exceeded”. If it’s not, it says “Budget OK”.
Let’s look at one more example:
Say you own a store and if customer purchased more than $1000, he gets a 5% discount.
Here’s the formula to do this:
=IF(A1>1000, A1-A1*5%, A1)
This function is a little different. Here, instead of printing a message, we are applying a mathematical formula to the cell. First, we’re testing the condition if a value is greater than 1000. If it is (i.e. the condition is true), we subtract 5% from that value. If the condition is not true, i.e. no discount for the customer, we’ll just print the original value.
Ready to jumpstart your Excel training? Check out this course on Excel for beginners and intermediate users.
Nested IF Functions
So far we’ve used the IF function to test only one condition. But what if you need two or more conditions? Then we’ll use the nested IF functions. This means we’ll use one (or more) IF functions inside of another IF function. We can use up to 64 nested IF functions. Sounds complicated? Well, it is, but once you understand how it’s working and try a few examples, you’ll be able to handle multiple IF functions quite easily.
Here’s the syntax for nested IF functions:
IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))
In words, it works this way:
Basically, if the condition is NOT TRUE, we’ll add more IF functions.
Say you want to check whether a value is equal, greater than or less than another value. If the value in cell A1 is less than B1, then type “A < B”. If the value in cell A1 is equal to B1, then type “A = B”. Finally, if the value in cell A1 is greater than B1, then type “A > B”.
Here’s the formula:
=IF(A1<B1,”A < B”, IF(A1=B1, “A = B”, “A > B”))
First, we check if A2 is less than B2. If that is true, then type “A < B”. If that condition is not true, use another IF function. Here we’re checking if A2 is equal to B2. If that is true, then type “A = B”. If that condition is not true, then type “A > B”.
Another good example of using nested IF functions is to assign grades to students.
A -> 90 or above
B -> between 80 and 89
C -> between 70 and 79
D -> between 60 and 69
F -> below 60
Here’s the formula:
=IF(B2>=90, “A”, IF(B2>=80, “B”, IF(B2>=70, “C”, IF(B2 >=60, “D”, “F”))))
Neat, right? Nested IF Functions are especially useful when working with a lot of data. With some VBA programming, you can even automate the entire process and make your work far faster. To learn more about nested IF functions, check out this Excel course for basic and advanced users.
Have some tips and tricks for using the IF function? Let us know in the comments below!