Excel IF Function (Table of Contents)
IF Function in Excel
The IF Function is the most popular function in Excel and it allows to make logical comparisons between a value and what you expect the output. We all know Excel is used for different types of reports. Some reports may contain huge data with lots of field in it. But it can happen that the report may not give you the desired result which is based on certain fields in the report. This is where Conditional formulas are used. If you need an outcome based on the criteria which depends on a field available in the report, you can use conditional formulas of excel.
There are many conditional formulas available in Excel. e.g. IF, OR, AND etc. but we will cover IF Function here.
The IF Function is an inbuilt function in Excel which is categorized as a Logical Function.
How to Use IF Function in Excel?
IF Function can be used as a worksheet function in Excel which means it can be entered as a part of a formula in a cell of the Worksheet. IF Function gives the outcome based on the conditional criteria for a field.
The Syntax for the IF Function in Excel is:
IF (Condition, Value_ if_ true, [ Value _ if _false])
Example #1
Let’s understand How to use IF Function in Excel using some examples.
Below is the data with Sales Target and Actual Sales of the respective employees.
There are 5 people in your team in Column A and you have given them the sales target for a month in Column B and their actual sales done is given in Column C, so with the given data you need to evaluate which person has achieved the target and who has not achieved the Target in Column D.
Now we will input the formula for Employee Shikha in Column D2- =IF(C2>=B2,”Target met “,”Target not Met”).
Let’s break the formula in three parts to get a better understanding.
- Condition
- Value IF TRUE
- Value IF FALSE
Now in our example,
Condition – C2>=B2
Value IF True – Target Met
Value IF False – Target Not Met
By dragging the C2 cell till C6 cell, we will get the result for all employees.
So, you can see that based on the data in Column B and C, we got to know which employee has MET TARGET and which has NOT MET TARGET.
Nested IF Statements
The IF Function can be “Nested” which refers to a formula where at least one IF Function is nested inside another to test for more than one condition which in return give more possible results. So, in “nested IF Statements” you must be very careful because each IF Statement needs to be nested inside another, so the logic is correct. You can refer the below example which will show you the usage of “Nested IF” Formula.
Example #2
In this Example, we have 5 students and their respective marks in 5 subjects which will give us the Total marks for each student and their % of Marks.
Now to know the grades of each student we will use the IF Function by using their % marks. We want to give the students 3 Grades-A, B & C.
We will use Formula in I2 Column- =IF(H2>=80,” A”, IF(H2>=70,” B”, IF(H2>=60, “C”))).
You can input the IF Formula in I2 Column and can autofill till I6. So, this is how we got the grades of the students by using the IF Function.
Example #3
We have the data of some comedians who came for shopping so here in below example, we will show you that IF their total bill is more than 5000 they will get 10% Discount. Let’s go through the example which will give a clear understanding of the use of IF function and Nested IF.
Now in our example, we will first show the use of IF Function and then Nested IF function.
Condition- C2>=5000
Value IF True – 10%
Value IF False – 0
Now we will input the IF Function Formula in E2 column, Syntax- =IF(C2>5000,”10%”,”0″). It will show who will get 10% Discount and who will not.
You can autofill rest of the E column till we have data.
Now we will show you how can we use Nested If Statement for the same example. In Nested IF formula, at least one IF Function is nested inside another to test for more than one condition.
Here the Conditions are
- Customer Bill>20000, “A”
- Customer Bill>10000, “B”
- Customer Bill <10000, “C”
Formula for Nested IF in our example is as follows
=IF(C2>20000,”A”, IF(C2>10000,”B”,”C”)).
By dragging the cell F2 till cell F9, we will get the result for all the customers.
In the above Example, the customer’s bill> 20000 will be category A, the customer’s bill>10000, it will be category B and the customer’s bill<10000, it will be category C.
Example #4
In this Example, an Employee has made the budget of the expenses for his Onshore Trip. In below screenshot, there is his budgeted data and the Actual amount spent by him.
Now in our example,
Condition- C2>=B2
Value IF True- Over Budget
Value IF False – In Budget
Now we will input the IF Function Formula in D2 Column to know whether his expenses are in his Budget or Overbudget.
Formula will be- =IF(C2>=B2,”Over Budget “,”In Budget”).
Things to Remember About IF Function in Excel
- IF Function is used to test or evaluate certain conditions and then give result differently depending on that condition.
- Up to 64 IF Functions can be Nested when you are using the Nested Formula.
- If any arguments to the IF are supplied as an array, the IF function will evaluate every element of that array.
- When you are applying the IF Formula and using text values in the formula, you need to enclose the text in double quotes (e.g. “Text”).
- The IF Function can be combined with Logical functions like AND & OR.
Recommended Articles
This has been a guide to IF Function in Excel. Here we discussed How to Use IF Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
