Formula denaming

Background

Opinion is divided between spreadsheet users who value Excel's ability to express formulas in terms of meaningful names, such as =Revenue-Costs, and others who prefer the traditional coordinate notation, such as =D5-D7.

If you like using names, you will sooner or later encounter a client or a partner who does not like the things.  And if you don't use names in your own work, in time you will be confronted by a spreadsheet that does make use of them.

OAK provides for this situation by providing the means to remove the names in Excel formulas and replace them with traditional coordinate references.  Because the action is in effect the reverse of the Formulas | Defined names | Defined name | Apply Names command, which is described in the Lesson E of the tutorial on Names, it is known as the Deapply names command.

Detail

Removing names from a formula is easy.  All one has to do is replace every instance of a name with its definition.  Consider a workbook in which

•a formula in cell Sheet1!K12  reads  =Revenue - Costs

•a name Revenue is defined as Sheet1!$D$5:$M$5

•a name Costs is defined as Sheet1!$D$7:$M$7.

In this case, the formula =Revenue - Costs  can be rewritten as  =Sheet1!$D$5:$M$5 - Sheet1!$D$7:$M$7.  And that rewritten formula will work perfectly well.  (If you don't understand quite why, read the description of Excel's row/column matching process described in Lesson F and Lesson I of the Names tutorial in this help.)

There is no circumstance under which a simple replacement of names by their definitions won't deliver exactly the same calculation as the version of the formula with names.  However, the problem with this rewritten formula is that it is far from idiomatic.  No one would write a formula like that.  They natural thing to write is =K5-K7.

When OAK removes names, it goes to considerable trouble to produce natural formulas.  Once it has transformed the original =Revenue - Costs into =Sheet1!$D$5:$M$5 - Sheet1!$D$7:$M$7, it further analyses the result to determine whether

•it can dispense with the worksheet names: it can in this case, because the formula is located on the same worksheet as the cells it is acting on

•Excel will engage its row/column matching mechanism: it will in this case, so OAK picks out the cells that would be the result of the match.

The result in this case would be =K5-K7, the formula that comes to mind most naturally.  In this way OAK delivers formulas which are natural and readable, and like the ones that an experienced analyst would write.

In addition, OAK Development | Names | Deapply really is symmetric with Excel's Formulas | Defined names | Defined name | Apply Names.command, in that it will round-trip: one can

•take an ordinary formula , such as  =D5-D7  in the above example,

•use Formulas | Defined names | Defined name | Apply Names to turn it into a names-based equivalent  =Revenue-Costs

•then use OAK Development | Names | Deapply to get =D5-D7 again

One does not get =Sheet1!$D$5:$M$5 - Sheet1!$D$7:$M$7, a mess full of sheet names and dollar signs that relies on column matching to get the answer first thought of.

Further denaming

The technologies that underpin OAK's Deapply names command are also used elsewhere in OAK, when reconstructing calculations and performing discrepancy analysis.  These functions require OAK to work out which cells are involved in a sequence of calculations, whether they are referred to by simple coordinate reference or by name.  OAK needs to anticipate Excel's row and column matching behaviour, so that it can pin down the cells involved exactly (D5 and D7 in the above example), rather than to something near but not quite exact  (D5:M7-D5:M5 in the same example.)