EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Logical Functions in Excel Tutorial Excel IF Function
 

Excel IF Function

Madhuri Thakur
Article byMadhuri Thakur

Updated July 4, 2023

What is the Excel IF Function?

The IF() function is a logical function in Excel that evaluates a specific condition and provides a certain value if the condition is True and another value if it’s False.

For example, the formula =IF(A6>B6,” Pass”,” Fail”) displays the output as Pass if the marks obtained (A6) are greater than the passing marks (B6); otherwise, display the output as Fail.

 

 

Excel IF Function

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

The Excel IF function is found in Excel’s Logical Functions library. We can use the function to analyze data, calculate salaries, prepare student academic reports, etc.

Excel IF Function Syntax

The syntax for the IF function is-

IF Function syntax

Explanation:

  • Logical_test: Test/ compare values using logical operators
  • Value_if_true: Value to be returned if the condition is satisfied
  • Value_if_false: Value to be returned if the condition is not satisfied

Key Highlights

  • The operators in the logical_test argument of the IF function may be:

           =   Equals to

          <> Not Equals to

           > Greater than

           >= Greater than or equal to

           < Less than

           <= Less than or equal to

  • Every conditional test must contain at least one comparison operator.
  • The IF function allows Excel spreadsheets to make decisions when analyzing data.
  • The IF function can have more than one logical test in one formula.

How to Use Excel IF Function?

You can download this Excel IF Function Template here – Excel IF Function Template

To understand the use of the IF function in Excel, consider the below-given example.

The table below shows the sales achieved by five sales representatives along with their monthly target. We want to find who has achieved the target and who has not achieved the target using the Excel IF function.

Question

Solution:

Step 1: Place the cursor in cell D6 and enter the formula,

=IF(C6>=B6,”Target Achieved”, “Target Not Achieved”)

Step 1

Explanation:

  • C6>=B6: It represents the criteria for displaying the corresponding result if the Sales Achieved are greater than or equal to the sales target.
  • The output to display when the criteria are met is “Target Achieved.”
  • “Target Not Achieved” is the output to display when the criteria are unmet.

Step 2: Press Enter key to get the below result

Step 2

The IF function analyzes and finds that Sales Achieved (90000) exceed the Sales Target (75000) and displays the output Target Achieved.

Now, to get the status of the remaining Sales representatives,

Step 3: Drag the formula into the other cells

Excel IF Function - Step 3

In the above table, the sales achieved by Michael, i.e., 48000 (C7), are not greater than or equal to the sales target, i.e., 50000 (B7), which means the given criteria are not met. Therefore, the IF function displays the status of Micheal as Target Not Achieved (highlighted in yellow).

Nested IF Statements

When there is more than one IF function in a formula to test certain conditions, it is known as a Nested IF statement. We can use Nested IF statements (formulas) to test more than one condition, giving more than one possible result. Consider the example below to understand the use and work of Nested IF functions.

Example #1

The below table shows the marks, total marks, and the percentage of five students in the subjects- English, Science, History, and Geography. We want to evaluate the student’s respective grades based on their percentages using the Nested IF function.

Excel IF Function-Example 1

Solution:

Step 1: Place the cursor in cell H6 and enter the formula,

=IF(G6>=80,”A”,IF(G6>=70,”B”,IF(G6>=60,”C”)))

Example 1 step 1

Note: In the above table, three IF functions exist in one formula, which is known as a Nested IF statement.

Explanation:

The formula states that:

  • If the percentage (G6) is greater than or equal to 80, then display the grade as A.
  • If the percentage (G6) is greater than or equal to 70, then display the grade as B.
  • If the percentage (G6) is greater than or equal to 60, then display the grade as C.

Step 2: Press Enter key to get the below result

Example 1 step 2

Explanation:

The percentage obtained by Ashu is greater than 70. Therefore, the IF function displays the output as B.

Now, to evaluate the grade obtained by other students

Step 3: Drag the formula to other cells

Excel IF Function-Example 1 step 3

Example #2

The table below shows a list of customers with the number of items they purchased and the total bill for each. If the total bill exceeds or equals $5000, the customers are offered a 10% discount on their purchase. We want to determine which customers are eligible for the discount using the Excel IF function.

Excel IF Function-Example 2

Solution:

Step 1: Place the cursor in cell E6 and enter the formula,

=IF(C6>5000,”10%”,”0″)

Example 2 step 1

Explanation:

  • C6>5000: The criteria indicate that the customer is eligible for a discount if the Total Bill (C6) amount exceeds 5000.
  • “10%”: The value to be displayed if the criteria are satisfied, i.e., the eligible customer will get a 10% discount.
  • “0” is the value displayed if the criteria are not satisfied, i.e., the customer will not get a discount.
Note: A text in IF (or any other) formula must be enclosed in double quotation.

Step 2: Press Enter key to get the below result

Example 2 step 2

The IF function evaluates the criteria and finds that the Total Bill of Jenny exceeds 5000. Therefore, it provides the result of 10% (discount).

Now, to find eligibility for other customers

Step 3: Drag the formula to other cells to get the below result

Example 2 step 3

The IF function evaluates and finds that the Total Bill of Mary is not greater than 5000. Therefore, it provides the result of 0 (highlighted in yellow), which indicates Mary is not eligible for a discount.

Based on the Total Bill amount, we want to categorize the customers as Premium, Gold, or Platinum using the Nested IF function.

Step 4: Place the cursor in cell F6 and enter the formula,

=IF(C6>=25000,”Platinum”,IF(C6>=15000,”Premium”,IF(C6>=4500,”Gold”)))

Example 2 step 4

Explanation:

  • C6>=25000, “Platinum”: It indicates that if the Total Bill (C6) is greater than or equal to 25000, then categorize the customer as Platinum.
  • C6>=15000, “Premium”: It indicates that if the Total Bill (C6) is greater than or equal to 15000, then categorize the customer as Premium.
  • C6>=4500, “Gold”: It indicates that if the Total Bill (C6) is greater than or equal to 4500, then categorize the customer as Gold.
Note: In the above example, the IF function is used thrice to evaluate three different criteria; therefore, it is known as Nested IF.

Step 5: Press Enter key to get the below result

Example 2 step 5

The IF function evaluates the criteria and finds that Jenny’s Total Bill (C6) amount equals 25000. Therefore, it provides the result as Platinum.

Step 6: Drag the formula in other cells to get the below result.

Excel IF Function-Example 2 step 6

Example #3

The table below shows the total budget and amount a traveler spends on expenses- Airfare, Hotel, Food, Fuel, and Shopping. We want to determine if he was Over Budget or In Budget using the Excel IF function.

Excel IF Function-Example 3

Solution:

Step 1: Place the cursor in cell D6 and enter the formula,

=IF(C6>B6,”Over Budget”,”In Budget”)

Example 3 step 1

Explanation:

  • C6>B6: It is the criteria that indicate if the Amount Spent (C6) is greater than Budget Amount (B6) and then provides a certain result.
  • “Over Budget”: It is the value to display if the criteria are satisfied.
  • “In Budget”: It is the value to display if the given criteria are unsatisfied.

Step 2: Press Enter key to get the below result

Example 3 step 2

The IF function provides the result of Over Budget (D6) because the Amount Spent (C6) is greater than the Budget Amount (B6), i.e., the criteria are satisfied.

Step 3: Drag the formula into other cells to find the status of other expenses

Excel IF Function-Example 3 step 3

The IF function provides the status as In Budget (highlighted in yellow) for Fuel because the Amount Spent is less than the Budget Amount, i.e., the criteria are unsatisfied.

Things to Remember

  • We can nest up to 64 IF functions using the Nested Formula when we use Nested IF.
  • The function will evaluate each array element if we provide an array as arguments in the IF formula.
  • If we use text values in the IF formula, we must enclose it with double quotes (e.g., “Pass”).
  • We can combine the IF functions with logical functions like AND and OR to provide multiple criteria.

Frequently Asked Questions (FAQs)

Q1) What is the importance of the IF function?

Answer: The IF function is a decision-making statement that guides Excel to make calculative decisions based on the given criteria. The Excel IF function executes one code if it satisfies the given condition or executes another code if it does not satisfy the given condition.

Q2) What is the use of the IF function in Excel?

Answer: The IF function enables the logical comparison of values and provides specific results based on whether the condition is True or False. In addition, we can also use the IF function to identify blank data cells within the Excel worksheet. We can nest the ISBLANK function into the IF to find the blank cells. Identifying blank data cells can help prevent calculation errors by finding possible issues before doing the actual calculations.

Q3) What are the 3 parts of the IF function?

Answer: The 3 parts of the IF function are

  • A logical test- To perform the logical comparison using an operator sign
  • A value to return if the result of the logical test is TRUE;
  • A value to return if the result of the logical test is FALSE.

Q4) How many IF functions are there?

Answer: We can nest up to 64 different Excel IF functions. However, maintaining multiple IF statements can be difficult; therefore, ensuring we nest the formula with 100% accuracy to give correct results is important.

Recommended Articles

The above article is a guide to the IF Function in Excel. To learn more about logic-based functions, EDUCBA recommends the below-given articles.

  1. COUNTIF Excel Function
  2. Nested IF Formula in Excel
  3. VBA IF Statements
  4. SUMIF Formula in Excel

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download Excel IF Function Template

EDUCBA Login

Forgot Password?

EDUCBA

Download Excel IF Function Template

EDUCBA

डाउनलोड Excel IF Function Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW