Named Formula
General
When working with spreadsheets, you usually create a magnitude of formulas. Having comprehensive and verifyable formulas is crucial for the understanding and maintenance of any spreadsheet. Most experienced users have therefore already switched to names instead of cell references.
Beware of the monsters!
Formula monsters usually consist of many nested functions, easily with a length of up to 100 characters or more. On top of the length comes the pain to keep track of the nested functions; in combination with relative references you will most miserably fail to decode the meaning of the whole formula's original meaning and why it has been constructed this way.
Call me names!
The first good habit when writing comprehensible formulas is to assign names to all ranges—or use built-in mechanisms like in the new table object in Microsoft Excel. The consistent use of named ranges will give you formulas like
=IIF(Turnover>Threshold;Discount;0)
And now: name your formulas?
You may well know already, that you can invoke the dialog for naming cells and ranges with Ctrl+F3. Defining a name will open a dialog like this:
Here you are specifying a name, a referenced range and the scope, in which the name is usable. The good part, which is not so obvious and therefore less known under spreadsheet experts, is, that you can "abuse" the field for the reference also as container for your formula! I call this concept "named formula".
First example: running number
Given a table with a running number, which should automatically be calculated based on the start value of 1. Usually you would do something like writing the value 1 into the first cell—here: B2— writing the formula =B2+1 in B3 and copying that formula until the end of your column.
Let's create our first named formula by following these verbose instructions:
- Enter the value 1 into the cell B2
- Go to the cell B3
- Open the name management dialog (best by pressing Ctrl+F3)
- Click on the Add or New button, depending on your spreadsheet software
- Specify
- the name __Next
- the formula =B2+1 where you would usually just enter the range (with or without a leading equal sign depending on your spreadsheet software)
- the scope, if necessary
- Hit the Add / New button
Now close the name manager and enter the formula =__Next into cell B3—it calculates the expected value 2. It is safe now to copy the same formula into all cells of your column and you will get your running number.
I personally prefer to write the cell references in named formulas in R1C1 notation while using the INDIRECT function, as it expresses the meaning a little bit better; in this case I would write in LibreOffice Calc
INDIRECT("R[-1]C";0) + 1
Now the formula says clearly "take the value from the row above in the same column and add 1".
If you compare the two methods, you can already see the first benefit of named formulas: you can easily visually tell, whether the correct (named) formula has been applied everywhere consistently:
Second example: Lookup
A typical task in bigger spreadsheets is to look up several values in a table given a unique identifier. Formula-wise you would realize a lookup with retrieval of the index
MATCH(Identifier;IdentifierColumn;0)
and the retrieval of the value with
INDEX(ValueColumn;Index)
where Index is a placeholder for the aforementioned formula. Most spreadsheet users use VLOOKUP for value retrieval from a table, but the function has one very big disadvantage: it is not resilient against the rearrangement of columns, as it requires a column parameter as number.
As you might have already guessed from this "two-stage-approach", you will create two named formulas, one named Index and the other Value. There is only one question open: where do you find the value of the unique identifier? Well, it depends on the scenario you are using this lookup mechanism. If it is a lookup, which happens in each row of your table, you need to specify the column with the identifier to look up, if it is a single lookup based on, say, a combo field, where you selected the identifier, you need to specify the cell, which contains the identifier. In a "lookup-per-row" scenario the result would look like the following:
"Nothing to be seen here" you say—and you are quite right. We still have the advantage of consistency, but this would not rectify the effort to create named formulas. This example was just for the sake to illustrate the nesting of functions.
Third example: slicing a monster
Let's see how named formulas can ease the understanding and management of complex formulas. You can retrieve the name of the current workbook in Microsoft Excel without using Visual Basic for Applications (VBA) with the following formula:
IF(CELL("Filename", INDIRECT("RC",FALSE))="", "", MID(CELL("Filename", INDIRECT("RC",FALSE)), FIND("",CELL("Filename", INDIRECT("RC",FALSE)))+1, FIND(".xl", CELL("Filename", INDIRECT("RC",FALSE))) - FIND("[", CELL("Filename", INDIRECT("RC",FALSE)))-1))
The formula is self-explanatory, right? No? Okay, the short description of its functioning is "If the filename is empty, the workbook must be empty as well; otherwise the workbook name is the part after the opening square bracket and ends before the file extension begins". Now let's put the named formulas to work: We will create one for each sub task: the file name, the position of the opening square bracket and the beginning of the file extension:
| Name | Formula |
|---|---|
| __Filename | CELL("Filename",INDIRECT("RC",FALSE)) |
| __PosSquareBracketOpen | FIND("[",__Filename) |
| __PosFileExtension | FIND(".xl",__Filename) |
Now that these named formulas are defined, the formula for __Worksheet reads like this
IF(Filename="", "", MID(Filename, PosSquareBracketOpen+1, PosFileExtension-__PosSquareBracketOpen-1))
Now you can easily match the above described functioning with the formula.
Fourth example: Matrix Bullet Time
Named formulas are also a nice way to store little matrices for lookup purposes while at the same time avoiding accidental changes. As you might already know, you can write a matrix in "colon-semicolon-form" like
{"ID","Name","Value";1,"Apple",200;2,"Banana",500}
in order to use a range
| ID | Name | Value |
|---|---|---|
| 1 | Apple | 200 |
| 2 | Banana | 500 |
Storing lists and matrices as named formulas comes in handy in at least two scenarios:
- Lookup ff you need lists of lookup values, which are rather static and should be kept away from the user in order to avoid accidental modification.
- Predefined Tables ff you need built-in or custom matrices, which act like built-in and custom lists, but provide more dimensions.
Just keep in mind, that these lists and matrices are just a little bit more obfuscated but not completely hidden, but that this is a good way to avoid "hidden worksheets". An experienced user will easily find the defined name and is also able to change a list's or matrix' content.
If you want to use the content of your named lists and matrices, don't forget to enter the reference with Ctrl+Shift.
Fifth example: Macro
Just as food for thought: How about putting an Excel-4-Macro-Function into a defined name?
Advantages
There used to be a very cool concept in Lotus Improv, which never made it into the mainstream: named formulas. By abusing the naming facility of your spreadsheet application like Microsoft Excel and LibreOffice Calc, you are finally able to imitate that functionality. Splitting your formula into several sub formulas also eases the change process, as changing, for example, the calculation of a certain value, will automatically be used in all formulas, which made use of that value. It can no longer happen, that you inadvertently forgot to change a few formulas, as they are textually and logically the same all over your workbook or worksheet. Named formulas, lists and matrices are more resilient against typos, malevolent and accidental changes and, as an additional benefit, can be documented easily by inserting the defined names into a separate worksheet for reviews and quality assurance.
Downsides
Creating a named formula is not natively supported, as it is a tweak in a spreadsheet application. Therefore you will usually create the formula in your worksheet and iteratively turn it into named formulas. Debugging such a formula is not as visual and easy as debugging a normal formula. Named formulas do not accept parameters, except for predefined fixed ranges like "the value left of me" or a named range.
Finally
Do you have any further thoughts on this, questions or other useful examples? Go ahead and leave a comment.
