|
When will we be 10,000 days into the millenium that started on 1 Jan 2000? Excel can easily calculate that, from the formula =MilleniumEnd+20000.
Excel might interpret this formula by
| • | looking up the definition of MilleniumEnd, which is (say) =Sheet1!$A$1 |
| • | looking inside cell Sheet1!$A$1, and finding 31 Dec 1999 stored as a datecode |
| • | displaying the answer, which should be formatted as a date |
The process of evaluating MilleniumEnd is a two stage process, in that Excel has to look up the definition to see that is Sheet1!$A$1, and then look in that cell to dig out a value.
Excel permits this process to be reduced to a one stage process. Rather than defining MilleniumEnd as cell A1, and storing 31 Dec 1999 in that cell, one can associated MilleniumEnd with 31 Dec 1999 directly. Here is how.
| 1 | Invoke Excel's Insert | Name | Define command. |
| 2 | The upper field, labelled Names in workbook, will be empty, or may have some piece of text in it that Excel has found near to the cells that happen to be selected, and offered as a possible candidate for the name to be defined. Either way, type into that field MilleniumEnd |
| 3 | Press Tab a couple of times to get to the lower field, labelled Refers to. That will have been prepopulated by Excel with the address of the cells that happen to be currently selected ,since most times this command is used, it is the current selection that is to be named. But here, we don't want to name that selection; we want to name 31 Dec 1999. Replace the cell address by typing in that date. |
| 4 | Press OK to dismiss the dialog box. |
Now Excel can interpret =MilleniumEnd+20000 more directly, by
| • | looking up the definition of MilleniumEnd, which this time it finds is 31 Dec 1999 without any need to look in any cells |
| • | displaying the answer, which should be formatted as a date. |
Naming a constant in this way allows assumptions to be provided to a spreadsheet without providing any cells for it. That can be a good thing; it means that the spreadsheet is not cluttered with assumptions any more than necessary, and are out of harm's way in terms of unwise alteration by unskilled users of the spreadsheet. But it can be a bad thing; the spreadsheet has assumptions that are not obvious. It is best confined to constants that really won't ever change, like the date of the end of the millenium or the speed of light.
A good use is in error messages. It's quite common to write =IF(ABS(A1)>0.0001,B2/A1,"N/A"); but that might be better written as =IF(ABS(A1)>0.0001,B2/A1,MessageNa). Then MessageNa can be defined as ="N/A". At a later time, that name can be redefined to some other text string, which might be more appropriate in another language. If there are thousands of formulas like that, scattered over several worksheets, making the equivalent change manually would be painful.
See also
Names tutorial M
|