Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Excel Frequency Function Example
  • Managing Conditional Formatting Rules in Excel
  • Example of COUNTIFS with variable table column in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Excel Bar Chart

References

  • How to get last row in text data in Excel
  • How to get address of named range in Excel
  • Extract all partial matches in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • How to get first row number in range in Excel

Data Validations

  • Excel Data validation must contain specific text
  • How To Create Drop-down List in Excel
  • Excel Data validation only dates between
  • Data validation must not exist in list
  • Excel Data validation exists in list

Nested IF function example in Excel

by

This tutorial shows how to calculate Nested IF function example in Excel using the example below;

Formula

=IF(T1,R1,IF(T2,R2,IF(T3,R3,IF(T4,R4,R5))))

Explanation

In the code above, T1-T5 represents 5 different logical tests, and R1-R5 represents 5 different results. You can see that each IF function requires it’s own set of parentheses.

This article describes the Excel nested IF construction. Usually, nested IFs are used when you need to test more than one condition and return different results depending on those tests.

Testing more than one condition

If you need to test for more than one condition, then take one of several actions, depending on the result of the tests, you can nest multiple IF statements together in one formula. You’ll often hear this referred to as “nested IFs”.

Worked Example:   Roll back weekday to Friday base on a particular date in Excel

The idea of nesting comes from embedding or “nesting” one IF function inside another

In the example shown, we are using nested IF functions to assign grades based on a score. The logic for assigning a grade goes like this:

Score Grade
0-63 F
64-72 D
73-84 C
85-94 B
95-100 A
Worked Example:   Running count group by n size in Excel

To build up a nested IF formula that reflects this logic, we can start by testing to see if the score is below 64. If TRUE, we return “F”. If FALSE, we move into the next IF function. This time, we test to see if the score is less than 73. If TRUE, we return “D”. If FALSE, we move into yet another IF function. And so on.

Eventually, the formula we have in cell D5 looks like this:

=IF(C5<64,"F",IF(C5<73,"D",IF(C5<85,"C",IF(C5<95,"B","A"))))

You can see that it’s important in this case to move in one direction, either low to high, or high to low. This allows us to return a result whenever a test returns TRUE, because we know that the previous tests have returned FALSE.

Worked Example:   How to display conditional message with REPT function in Excel

Making nested IFs easier to read

By their nature, nested IF formulas can be hard to read. If this bothers you, you can add line breaks inside the formula to “line up” the tests and results.

Post navigation

Previous Post:

VLOOKUP without #N/A error in Excel

Next Post:

How to Create Gantt Chart in Excel

Leave a Reply Cancel reply

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

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel XOR Function
  • How to use Excel NOT Function
  • Nested IF function example in Excel
  • How to use IFS function in Excel
  • IF, AND, OR and NOT Functions Examples in Excel

Date Time

  • Get age from birthday in Excel
  • How to calculate quarter from date in Excel
  • Calculate series of dates by workdays in Excel
  • Get first day of month in Excel
  • Steps to create Dynamic calendar grid in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Sum by group in Excel
  • How to Delete Cells, Row and Rows in Excel
  • Flash Fill in Excel
  • Print Excel Sheet In Landscape Or Portrait
  • Check if multiple cells have same value in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning