In earlier lessons, we have seen that Excel will resort to matching rows and columns where necessary to make sense of formulas that involve names, but that behaviour is unnecessary when dealing with functions that aggregate their inputs.
Most of the time that is appropriate behaviour, but there are occasions when it is not.
In the example below, a spreadsheet is seeking to partition net earnings between a positive part, labelled profit, and a negative part, labelled loss.
.
| 1 | Define a name Earnings to refer to cells B2:F2. You can do this, |
| • | either, by selecting cells B2:F2 and using Excel's Formulas | Defined names | Define name command |
| • | or, by selecting cells A2:F2 and using Excel's Formulas | Defined Names | Create from selection command with the Left column option activated. |
| 2 | Select any one cell. |
| 3 | Use Excel's Formulas | Defined names | Defined name | Apply Names command to apply the just-made name Earnings. |
| 4 | See that Excel has changed the formula in line 6 to =MAX(0,-Earnings), but it has made no change to the formula in line 4. |
We learned about Excel's Apply names command in Lesson E, where we saw:
TIP: The golden rule is that Apply names never makes a substitution that would cause the meaning of a formula to change. Every time that you expect Excel to substitute a name for some coordinates in your formula, and it doesn't, is an opportunity to revisit your understanding of what the formula would have meant had the substitution happened, and why it is not the same as what you have written in coordinate form.
So why does ApplyNames decline to change =MAX(0,F3) to =MAX(0,Earnings)? The reason is that MAX(0,F3) is finding the higher of two numbers. MAX(0,Earnings) would find the higher of six numbers, the zero and the five values in the range now named Earnings. Excel does not adjust the formula because it detects that to do so would be to change the meaning of the formula.
MAX(0,Earnings) would be equivalent to MAX(0,F3) if Excel performed a column match on Earnings. But as we learned in the last lesson, MAX is one of the functions that aggregates its inputs, and renders column matching unnecessary.
Why, then, did Excel make a name substitution in the other formula? What was =MAX(0,-F3) has become =MAX(0,-Earnings). Excel would only change the formula if it was safe to do so. And it is safe to do so: while MAX is an aggregating function, which causes Excel not to column match, the minus sign, or any other mathematical operation, has the effect of re-enabling it.
This being so, it's easy to get Excel to use names in the Profit formula also. Simply change =MAX(0,F3) to =MAX(0,+F3). Once that change is made, Excel will happily apply the name Earnings, so that the formula becomes =MAX(0,+Earnings).
Remember
| • | =MAX(0,Earnings) finds the largest of six numbers, the zero and the five numbers named Earnings |
| • | =MAX(0,+Earnings) finds the largest of two numbers, the zero and whichever of the Earnings numbers is selected by column matching. |
See also