In this video, we're going to look at how to make a nested IF formula more readable, by adding line breaks.
Here I have a worksheet that calculates sales commissions, based on the commission structure shown in the table.
For example, we can see that King sold $124,500, and gets a commission of 5%, worth about $6,000.
On the left, I have have a small test area, with three formulas to calculate the commission rate. You can see that each formula returns the same rate when we plug in different sales numbers.
So, if we look at the first formula, you can see it's created with series of nested IFs. Like all nested IFs, it's difficult to understand what the formula is doing at first glance.
Let's use white space to make the 2nd formula easier to read.
One trick you can use to understand a formula is to use the formula tip window, which appears when you put the cursor into a formula. In this case, we can click into the first IF statement and use the formula tip window to select the logical test, the value if true, and the value if false.
If we click into the next IF statement, we can do exactly the same thing.
So, you can see that nested IF statements follow a pattern. If this, then than, otherwise, if this, then that, otherwise, if this, then that,... and so on
By adding white space to the formula, we can expose this pattern visually.
To do this with a nested IF, first make the formula bar a lot bigger. We need to be able to see more than one line.
Then, click into the first IF statement, and use the tip window to select the value if true. Next, use the right arrow key to step one character to the right, and add a new line. On the Mac, add a new line with Control Option return. On windows, use Alt Enter.
Do the same thing for each IF statement.
You might also want to add a new line after the equal sign at the start, so that all statements line up perfectly.
When I press return, the formula works the same as the original.
But the new formula is easier to read and edit. It actually looks like the commission table, so it's a lot easier to find and change specific values.
So remember that Excel doesn't care about extra white space — you're free to add new lines or extra space as you like.
In the next video, we'll look at the final formula, which uses the VLOOKUP function. I'll show you why this is an even better approach.
The IF function can perform a logical test and return 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....