How To Create Multiple If Formula In Excel
- Login
- Cart
- Training
- Videos
- Functions
- Formulas
- Shortcuts
- Blog
Nested IF function example
Generic formula
= IF (T1,R1, IF (T2,R2, IF (T3,R3, IF (T4,R4,R5))))
Summary
The IF function can be nested inside of itself to handle multiple conditions. In the example shown, a nested IF formula is used to assign a grade to a score. The formula in D5 contains 5 separate IF functions:
= IF (C5< 64 , "F" , IF (C5< 73 , "D" , IF (C5< 85 , "C" , IF (C5< 95 , "B" , "A" ))))
Explanation
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, one option is to 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 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. This video explains how to add line breaks to a nested if.
Notes
- The newer IFS function can handle multiple conditions in a single function.
- VLOOKUP can sometimes be used to replace complicated nested ifs.
- This article has many more examples of nested ifs.
Related functions
The IF function runs a logical test and returns one value for a TRUE result, and another for a FALSE result. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...
Related videos
In this video I'll show you how to create a formula that uses multiple, nested IF statements. This is a common technique to handle multiple conditions in a single formula.
In this video we'll look at how to use the IF function, one of the most powerful and popular functions in Excel.
By their nature, nested IF formulas are hard to read. In this video we show you how to easily make a nested IF formula more readable by adding line breaks.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
Excel video training
Quick, clean, and to the point.
Learn more
How To Create Multiple If Formula In Excel
Source: https://exceljet.net/formula/nested-if-function-example
Posted by: brittfrok2001.blogspot.com
0 Response to "How To Create Multiple If Formula In Excel"
Post a Comment