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

Data Analysis

  • How to create Checklist in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • How to Create Area Chart in Excel
  • Understanding Pivot Tables in Excel

References

  • How to get last row in numeric data in Excel
  • Count rows with at least n matching values
  • Convert text string to valid reference in Excel using Indirect function
  • Create hyperlink with VLOOKUP in Excel
  • How to get address of first cell in range in Excel

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation allow uppercase only
  • Excel Data validation must contain specific text
  • Excel Data validation with conditional list
  • Prevent invalid data entering in specific cells

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

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

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.

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:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

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 FALSE Function
  • Invoice status with nested if in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel NOT Function
  • How to use IFS function in Excel

Date Time

  • Convert decimal seconds to Excel time
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate Next working/business day in Excel
  • Get month name from date in Excel
  • Get project midpoint in Excel

Grouping

  • How to randomly assign people to groups in Excel
  • How to randomly assign data to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • Map inputs to arbitrary values in Excel
  • Group times into unequal buckets in Excel

General

  • Basic text sort formula in Excel
  • How to fill cell ranges with random text values in Excel
  • Count cells that do not contain many strings in Excel
  • Create dynamic workbook reference to another workbook in Excel
  • Select, Insert, Rename, Move, Delete Worksheets in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning