Up to Excel , Excel allowed up to 7 levels of nested IFs. However, just because you can nest a lot of IFs, it doesn't mean you should. Every additional level you add makes the formula more difficult to understand and troubleshoot. If you find yourself working with a nested IF more than a few levels deep, you should probably take a different approach — see the below for alternatives.
One of the challenges with nested IFs is matching or "balancing" parentheses. When parentheses aren't matched correctly, your formula is broken.
Luckily, E xcel provides a couple tools to help you make sure parentheses are "balanced" while editing formulas. First, once you have more than one set of parentheses, the parentheses are color-coded so that opening parentheses match closing parentheses. These colors are pretty darn hard to see, but they are there if you look closely:. Second and better when you close a parentheses, Excel will briefly bold the matching pair.
You can also click into the formula and use the arrow key to move through parentheses, and Excel will briefly bold both parentheses when there is a matching pair.
If there is no match, you'll see no bolding. Unfortunately, the bolding is a Windows-only feature. If you're using Excel on a Mac to edit complex formulas, it sometimes makes sense to copy and paste the formula into a good text editor Text Wrangler is free and excellent to get better parentheses matching tools.
You can paste the formula back into Excel after you've straightened things out. When it comes to navigating and editing nested IFs, the function screen tip is your best friend. With it, you can navigate and precisely select all arguments in a nested IF:. You can see me use the screen tip window a lot in this video: How to build a nested IF.
Just as a quick reminder, when working with the IF function, take care that you a properly matching numbers and text. I often see formulas IF like this:. Is the test score in A1 really text and not a number? Then don't use quotes around the number.
Otherwise, the logical test will return FALSE even when the value is a passing score, because "" is not the same as If the test score is numeric, use this:. When you're working with a formula that contains many levels of nested IFs, it can be tricky to keep things straight. Because Excel doesn't care about "white space" in formulas i. For example, the screen below shows a nested IF that calculates a commission rate based on a sales number. Here you can see the typical nested IF structure, which is hard to decipher:.
However, if I add line breaks before each "value if false", the logic of the formula jumps out clearly. Plus, the formula is easier to edit:. Video: How to make a nested IF easier to read. Nested IFs are powerful, but they become complicated quickly as you add more levels.
For example, in the problem below, we want to put an "x" in column D to mark rows where the color is "red" and the size is "small". All of this could be done with nested IFs, but the formula would rapidly become more complex. For example, this nested IF assigns numbers to five different colors:. Sure, you could use a long and complicated nested IF to do the same thing, but please don't :.
The value returned will be for the first condition that is true. You can also specify a default value to be returned if none of the values matches the expression. You can see the difference between using IFS and accomplishing the same task with nested IF statements. You can imagine, as the number of options may increase, how complicated it would be to track the function nesting in Excel.
In the example below, you can see returning a value between 1 and 4 based on the result. In Example 1, rows 6 and 7 with the 58 grade demonstrate this. You can always ask an expert in the Excel Tech Community or get support in the Answers community. Formulas and functions. Need more help? Expand your skills. Get new features first. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help.
0コメント