This Excel tutorial will teach you how to create an Excel IF formula. If you have never created an Excel IF formula before then this tutorial will explain the basics of how to create one so you will be proficient in creating IF formulas in 15 minutes!
Excel IF functions can be used together with Excel OR functions and Excel AND functions.
What is an Excel IF Formula?
It is a logical function that is used to test if something is TRUE or FALSE. The IF function says that if something is true then do something otherwise do something else. To explain this in more detail this tutorial will provide you with examples of how to create an IF formula. You will discover that you can nest multiple IF functions (64 in total) and you will see an example of how to do this later on in the tutorial.
Excel IF Function Arguments
The syntax of the IF function is:
logical_test – This is a required argument and is a value or logical expression that can be either TRUE or FALSE. You can specify a value, date, number or any comparison operator
value_if_true – When the logical test is TRUE or the condition is met this is the value to return
value_if_false – When the logical test is FALSE or the condition is not met then this is the value to return
Note the value_if_true and value_if_false arguments are both optional. Only one or the other is optional at a time. If both arguments are omitted then it the function will return a zero.
Excel IF Formula Example
Now let's have a look at an example of how to create an Excel IF formula.
We have a list of sales people in America with their sales in their region. I want to give the sales people a bonus if their sales are over $10,000. To do this I will need to enter an Excel IF Formula in column D.
The formula in cell D2 is =IF(C2>10000,"Bonus","No Bonus"). The logical test condition is cell C2. I am testing if the sales in cell C2 is over $10,000. If this is true the value_if_true argument will return “Bonus”. If this condition is false then the value_if_false condition will return “No Bonus”. I then copy the formula down.
You can see that "Bonus" is assigned to the sales people who have exceeded $10,000 in sales and "No Bonus" to the sales people who have less that $10,000 sales.
Nested Excel IF Formulas
As I have mentioned earlier you can nest up to 64 IF functions to test more conditions and return more possible results. Each IF statement has to be carefully nested inside another. Let’s look at an example.
Suppose an Area Manager for the North is only interested in the sales people in his area. He wants to give a bonus only to the sales person in the North who have sales are over $10,000.
The formula in cell D2 is =IF(B2="North",IF(C2>10000,"Bonus","No Bonus"),"N/A"). The first IF function tests if the area in cell B2 is “North”. If this is TRUE then the second IF function tests if the bonus in cell C2 is over $10,000. If both conditions are met then it will return “Bonus” otherwise it will return “No Bonus”. Finally the value_if_false argument for the first IF function will return “N/A” if the area in cell B2 is not “North”. The formula is then copied down.
Even though you can nest up to 64 IF functions this is not recommended. The more IF functions you nest the more complicated the formula will be and the more difficult it would be to edit if it gives a wrong answer. To get around this problem it is advisable you use the Excel VLOOKUP function as it will be easier to manage. You can see my tutorial on the VLOOKUP function here.
Here is a list of logical operators you can test in an Excel IF function:
I hope you have enjoyed this tutorial on how to create Excel IF formulas. If you need any more help or have any questions then please leave a message in the comments section below and I will get back to you. I would love to hear from you.