• Skip to primary navigation
  • Skip to main content
  • Skip to footer
WallStreetMojo

Wallstreet Mojo

Wallstreet Mojo

MENUMENU
  • Resources
        • Excel

          • Excel Functions
          • Excel Tools
          • Excel Tips
        • Excel
        • Financial Functions Excel

          • NPV in Excel
          • IRR in excel
          • PV in Excel
        • Financial-Functions-Excel
        • Lookup Functions Excel

          • VLOOKUP
          • HLOOKUP
          • Index Function
        • Lookup-Functions-in-Excel
        • Excel Charts

          • Pareto Chart in Excel
          • Gannt Chart in Excel
          • Waterfall Chart in Excel
        • Excel-Charts
        • VBA

          • VBA Left Function
          • VBA Paste Special
          • VBA Worksheet Function
        • VBA
        • Others

          • Resources (A to Z)
          • Financial Modeling
          • Equity Research
          • Private Equity
          • Corporate Finance
          • Financial Statement Analysis
  • Free Courses
  • All Courses
        • Certification Courses

          Excel Course certificate
        • Excel VBA All in One Bundle

          Excel-VBA-Certification-Course
        • Excel Data Analysis Course

          Excel-Data-Analysis-Course
        • VBA Macros Course

          VBA-Training-Course
        • Others

          • Basic Excel Training
          • Advanced Excel Course
          • Tableau Certification Course
          • Excel for Finance Course

          • Excel for Marketers Course
          • Excel for HR Managers
          • Excel for Power Users
          • View All
  • Excel VBA All in One Bundle
  • Login

Excel Nested If Function

Home » Excel » Logical Functions in Excel » Excel Nested If Function

By Jyoti Singh Leave a Comment

Nested IF Condition in Excel

In excel nested if function means we use another logical or conditional function with the if function to test more than one conditions, for example, if there are two conditions to be tested we can use the logical functions AND or OR function depending upon the situation, or we can use the other conditional functions even more ifs inside a single if.

Nested IF Function in Excel

IF function in excel is simple when there is a single logical test, however, when there are more than one logical tests, then it becomes a little complicated. The logic of testing multiple IF conditions are called Nested IF Function in excel. In today’s article, we will exclusively concentrate on Nested IF Function in excel. This article includes a wide variety of nested IF formulas along with other two important logical functions AND & OR Function.

Examples of NESTED IF Function in Excel

The following examples are used to calculate the Nested IF Function in Excel:

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

Example #1 – NESTED IF Function in Excel

Now take a look at the popular nested IF example. Based on the students score we need to arrive their standards. Consider the below data for an example.

NESTED IF Example 1

In order to arrive the results we need to test below conditions and these conditions are nothing but our logical tests.

  • If the score is >=585 result should be “Dist”
  • If the score is >=500 result should be “First”
  • If the score is >=400 result should be “Second”
  • If the score is >=350 result should be “Pass”
  • If all the above conditions are FALSE then the result should be FAIL.

Ok, we have totally 5 conditions to test. The moment logical tests are more than on we need to use nested IF’s to test multiple criteria’s.

Step 1: Open IF condition and pass the first test i.e. test whether the score is >=585 or not.

NESTED IF Example 1-1

Step 2: Now if the above logical test is TRUE then we need the result as “Dist”. So enter the result in double quotes.

NESTED IF Example 1-2

Step 3: Now the next argument is if the value or test is FALSE. If the test is false I have 4 more conditions to test, so open one more IF condition in excel in the next argument.

NESTED IF Example 1-3

Step 4: Now test the second condition here. The second condition is to test whether the score is >= 500 or not. So pass the argument as >=500.

NESTED IF Example 1-4

Step 5: If this test is true the result should be “First”. So enter the result in double quotes.

NESTED IF Example 1-5

Step 6: We have already entered two excel IF conditions, if these two tests are FALSE then we need to test the third condition, so open one more IF now and pass the next condition i.e. test whether the score is >=400 or not.

NESTED IF Example 1-6

Step 7: Now if this test is TRUE the result should be “Second”.

Popular Course in this category
Cyber Monday Sale
All in One Excel VBA Bundle (35 Courses with Projects) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
4.9 (1,353 ratings)
Course Price

View Course

Related Courses
VBA Macros CourseAdvanced Excel CourseTableau Certification Course

NESTED IF Example 1-7

Step 8: Now the total number IF conditions are 3. If all these IF conditions test is FALSE we need one more condition to test i.e. whether the score is >=300.

NESTED IF Example 1-8

Step 9: If this condition is TRUE then the result is “Pass”.

NESTED IF Example 1-9

Step 10: Now we came to the last argument. Totally we have entered 4 IF’s, so if all these conditions tests are FALSE then the final result is “FAIL”, so enter “FAIL” as the result.

NESTED IF Example 10

This is how we can test multiple conditions by nesting many IF condition inside the one IF condition.

The logic here is first IF result will come if the logical test is TRUE if logical test is FALSE then second IF will be executed. Like this, until the formula finds the TRUE test result it will be executed. If none of the results is TRUE then the final FALSE result will be executed.

Example #2 – NESTED IF Function in Excel

Now take look at the real-time corporate example of calculating sales commission. Consider the below data for the example.

NESTED IF Example 2

In order to arrive the commission %, we need to test the below conditions.

  • If the sales value is >=7 lakh, commission % is 10%.
  • If the sales value is >=5 lakh, commission % is 7%.
  • If the sales value is >=4 lakh, commission % is 5%.
  • If the sales value is < 4 lakh, the commission is 0%.

This is very similar to the previous example. Instead of arriving results we need to arrive percentages as the result, let’s apply nested IF Function in excel.

Step 1: Apply IF and test the first condition.

NESTED IF Example 2-1

Step 2: Apply second IF, if the first test is FALSE.

NESTED IF Example 2-2

Step 3: If the above IF conditions are FALSE then test the third condition.

NESTED IF Example 2-3

Step 4: If all the above conditions are FALSE then the result is 0%.

Example 2-4

Step 5: Copy down the formula to the remaining cells, we will have results.

Example 2-5

Example #3 – NESTED IF Function in Excel

Take an example of how to use other logical function AND with IF condition to test multiple conditions.

Take the same data from the above example, but I have slightly changed the data, I have removed the Sales column.

Example 3

Here we need to calculate a bonus for these employees based on the below conditions.

  • If the employee’s department is Marketing & Year of service is >5 years then the bonus is 50000.
  • If the employee’s department is Sales & Year of service is >5 years then the bonus is 45000.
  • For all the other employees if the service is > 5 years, the bonus is 25000.
  • If the year of service is <5 years, the bonus is zero.

This looks a bit completed, isn’t it?

In order to arrive at a single result, we need to test two conditions. When we need to test two conditions and if both the conditions should be true the AND logical condition will be used.

AND will return the result is TRUE if all the supplied conditions are TRUE. If any of the one condition is FALSE then the result will FALSE only.

Step 1: Open IF condition first.

Example 3-1

Step 2: Since we need to test two condition to arrive the result lets open AND function inside the IF condition.

Example 3-2

Step 3: Here we need to test the conditions. The first condition is whether the department is Marketing or not and the second condition is a year of service is >=5 years.

Example 3-3

Step 4: If the supplied conditions are TRUE bonus amount is 50000.

Example 3-4

Step 5: Like this apply tests for remaining conditions. I have already applied the formula to arrive at the results.

Example 3-5

Things to Remember

  • AND will return the TRUE result if all the supplied conditions are TRUE. If anyone of the condition is FALSE then it will return FALSE as the result.
  • In order to arrive the final result, you need to apply one more rather you can pass the result in the FALSE argument only.

Recommended Articles

This has been a guide to Nested IF Function in Excel. Here we discuss how to use Nested IF Function in Excel along with practical examples and downloadable excel template. You may learn more about excel from the following articles –

  • Examples of Excel SUMIF Text
  • Examples of Excel SUMIF Not Blank
  • COUNTIF Function in Excel VBA
  • SUMIF with Multiple Criteria in Excel
  • IFERROR Function Excel
  • IF Formula Excel
  • IF AND Excel Function
  • COUNTIF in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Filed Under: Excel, Logical Functions in Excel

Reader Interactions
Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Footer
COMPANY
About
Reviews
Blog
Contact
Privacy
Terms of Service
FREE COURSES
Free Finance Online Course
Free Accounting Online Course
Free Online Excel Course
Free VBA Course
Free Investment Banking Course
Free Financial Modeling Course
Free Ratio Analysis Course

CERTIFICATION COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Business Valuation Course
Equity Research Course
CFA Level 1 Course
CFA Level 2 Course
Venture Capital Course
Microsoft Excel Course
VBA Macros Course
Accounting Course
Advanced Excel Course
Fixed Income Course
RESOURCES
Investment Banking
Financial Modeling
Equity Research
Private Equity
Excel
Books
Certifications
Accounting
Asset Management
Risk Management

Copyright © 2019. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

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.

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.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

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

WallStreetMojo

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

WallStreetMojo

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

WallStreetMojo

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

WallStreetMojo

Download Nested IF Function Excel Template

By continuing above step, you agree to our Terms of Use and Privacy Policy.

CYBER WEEK OFFER - All in One Excel VBA Bundle (35 Courses with Projects) View More