R1C1 Excel Musings – Defined Names

The topic this time is Defined Names. Also known (with varying degrees of accuracy) as Range Names, Named Ranges and Named Formulas, Defined Names in Excel can be a very powerful tool for adding convenience and functionality to our workbooks. After giving a quick introduction, I’ll be discussing some of the more advanced things we can do with them that I find interesting.

THE QUICK INTRODUCTION

I would expect that most people reading this have come across them before. Since Excel 2007, the Formulas tab on the ribbon has included a section called Defined Names and a tool called the Name Manager. At their simplest, a name can be given to a specific cell address, e.g. cell $C$3 on Sheet1 could be named TaxRate, which then enables us to use the syntax TaxRate in other formulas whenever we want to refer to cell $C$3 of Sheet1. It is this simple and common use, of providing a name to a cell address (also known as a range) that leads many people to call the Defined Names feature ‘Range Names’ or ‘Named Ranges’. However, if after assigning this name we open up the Name Manager and click on TaxRate, what we see down the bottom in the ‘Refers to:’ box is =Sheet1!$C$3. This looks just like something we might enter for a cell in the formula bar! And that’s because it is in fact a formula.

DEFINED NAMES ARE IN FACT NAMED FORMULAS

This is a very important conceptual leap the first time we become aware of it. Any formula that can be entered into a cell can also be entered as the definition of a defined name. A reference then made to that name by another formula (either from a workbook cell, or indeed another defined name) will then return the result evaluated by the formula that is our first defined name. For example, we could define a new name called DiceRoll and assign it the formula =RANDBETWEEN(1,6) (well, provided we are using Excel 2007 or later, as the RANDBETWEEN function was only introduced with Excel 2007). We can then type =DiceRoll in a cell in our workbook, and have a random whole number between 1 and 6 returned. We could define a second name, SumTwoDice, and assign it the formula =DiceRoll+DiceRoll. Referring to SumTwoDice in a cell will then simulate the sum of two dice. In fact any formula that can be put in a cell can be put in a defined name.

WHY OH WHY IS THIS AT ALL USEFUL?

Let me start my answer to this by saying that, like most of the more advanced features in Excel, there is a time and a place for using them, and a time and a place for avoiding them. I started my career as a spreadsheet model auditor, and from an auditor’s point of view it is generally preferable to keep defined names to a very low level of complexity, such as simple direct references to single cells and not much more. This is because auditors need to review every formula in a model, and having complex formulas embedded in a workbook’s defined names rather than in standard cells makes them harder to access and harder to review. For the developer, such complex formulas are also harder to create with the correct syntax since the auto-prompting by Excel that occurs when entering formulas in cells does not occur when entering formulas as defined names. There are several other commonly held opinions on the cons of complex defined names which I won’t list here, but are worth being aware of.

There are, however, some distinct advantages to embedding certain complex formulas within defined names rather than cells. One such advantage is that it can in certain circumstances save on workbook space through avoiding the need for ‘helper cells’. This isn’t normally a concern in financial modelling, but can be quite useful when Excel is being used to develop a spreadsheet that behaves like an application with one specific stable purpose rather than a cash flow model with multiple variable assumptions. Less helper cells through the use of defined names can mean a neater presentation, a more secure model and in some circumstances a materially faster calculation speed.

The second and larger advantage though is that named formulas automatically behave as array formulas (and they do so without the need for using Ctrl+Shift+Enter). This sentence might not mean much on first reading, but it opens up a plethora of new possibilities for things we can do. One aspect of array formulas that I did not go into in the last edition is that multi-cell array formulas can be of variable size. For example (here’s one for the life insurance folks), let’s say your spreadsheet asks you to enter your age-last-birthday as a user-variable assumption (call this age ‘n’), and you have a calculation that relies on the array {1,2,3,…,n}. Let’s also assume that the calculation is complex enough to need helper cells, and so we can’t do the entire calculation in one cell. Entering the variable sized array {1,2,3,…,n} into cells in a flexible way that caters for changes to n is not something that can be done neatly, as we have to fix in advance how many cells we enter the array into. Too few, and it doesn’t go all the way to n. Too many, and we received #N/A results in the extra cells. But if we enter this array as a defined name, it will automatically size itself to the correct size whenever it is used.

For example, let’s define the name increment and give it the formula =ROW(OFFEST(Sheet1!$A$1,,,n)). Increment is now a variable sized array that returns the numbers {1,2,3,…,n}, auto-sized to n elements regardless of the value of n. We can call increment in other formulas. Let’s say n = 10. If we type =COUNT(increment) in a cell, it returns 10. If we type =SUM(increment) in a cell, it returns 55 – useful if we ever forget that the sum of 1 to n is (n)*(n+1)/2. If we type using Ctrl+Shift+Enter =SUM(increment^2) in a cell, it returns 385, which is the sum of the first 10 square numbers. And if our assumption for n is changed from 10 to 20, this sum of squares result auto updates to 2870. If we want to avoid having to use Ctrl+Shift+Enter to get the sum of the first n squares, we can define a new name such as SumSquaresN and assign it the formula =SUM(increment^2). Now we can type =SumSquaresN into a cell without the need for Ctrl+Shift+Enter, making use of the fact that named formula automatically calculate as array formulas. And putting my auditor hat back on again, if for some reason I was auditing a calculation that relied on summing the squares from 1 to n, I would probably find this setup easier to review than the formula =n*(n+1)*(2n+1)/6, because I can see what it is the formula is actually intending to achieve.

OKAY, I’M STARTING TO SEE SOME USES. WHAT ELSE?

Aside from assisting in calculations that involve a single cell result and a variable- sized array in the interim steps, two other beneficial uses of the array nature of named formulas are (i) dynamic drop down lists, and (ii) charts that need a variable maximum value on the x-axis. Both of these features take advantage of the fact that arrays as defined names auto-size to a variable number of elements. Dynamic drop down lists are when the data-validation options given for a cell change depend on the value in some precedent cell, and I won’t explain them here. Let’s talk about the charts. A chart with a variable x-axis can be useful in many situations. For example, if we want to chart the forecast balance of a debt facility over time, or the split between interest and principal payments each month of a standard mortgage over time, and need to model this where the duration of the loan is variable. The key is to make the x-axis of the chart refer to a defined name that returns a variable array. Achieving this is tricky to stumble upon if you don’t know how, but fairly straight-forward to implement once you’ve been shown. The following page includes some advice on doing this. Using named formulas in this way to make dynamically sized charts can be extremely useful in improving the presentation and flexibility of our models.

AN OLD CHALLENGE AND A NEW CHALLENGE

There was a great response to the array formula challenge, with 26 people submitting entries. The most elegant solution provided was =MATCH(MAX(MMULT(data,TRANSPOSE(COLUMN(data)^0))),MMULT(data,TRANSPOSE(COLUMN(data)^0)),0).

This was submitted by eight different people. The winner of the book prize was Corey Plover. For a detailed write up of the challenge, see my summary at http://bit.ly/1obLuVg.

We have another challenge this issue, with a $50 Dymocks e-voucher sponsored by the Institute to be awarded to the best entry. This is a named formula / chart building challenge with quite a wide scope for creativity. The task is to prepare a chart using named formulas for the data, rather than worksheet cell references. Try and keep worksheet cells used to no more than setting assumption variables, and do all calculations in named formulas. The chart can be about anything you like – be it work related such as a loan profile chart, or leisure related such as a chart of the Batman logo. A zip file containing these exact two examples, along with some tips on constructing these charts, can be downloaded at http://bit.ly/1twN0mU. For the mathematically inclined, try charting Lissajous figures this way. Send me your submissions by email by 4 November, and I will judge a winner.

A FINAL NOTE, FOR FUN AND LEARNING

Some pretty amazing things can be done in Excel with defined names and charts when the software is pushed. For an extreme example, check out ‘Dancing Pendulums’ by fellow Excel guru Ian ‘Hui’ Huitson, based in Perth. Open up the Name Manager and see what can be done with defined names! You can download it at http://chandoo.org/wp/2011/07/06/3d-dancing-pendulums/

Chart approximating the Batman logo in Excel, using defined names as the chart source data

TIPS FOR MAKING CHARTS IN EXCEL THAT REFER TO DEFINED NAMES – BY DAN MAYOH

Here, I will walk you through how to make a chart approximating the ABC logo in Excel, using defined names as the chart source data.

For this particular chart, we will use an XY-scatter plot as the chart type. Ultimately before we can create the chart, we need to set up two defined names – one which will generate our series of x-axis points and one which will generate our series of y-axis points.

The ABC logo can be approximated as a parametric curve with the functions:
X = sin(t)
Y = cos(3t)

For t covering the range from 0 to 360 degrees, or 0 to 2*Pi radians. Since Excel works in radians, it will be useful to make use of the =PI() function in Excel to generate this range.

We’ll plot 360 data points, which will be enough to give us a smooth looking curve. The first step is therefore to generate an array that returns the numbers {1, 2, 3, …, 359, 360}. Let’s call this ‘increment’.

An easy way to achieve this is to define a name ‘increment’ with the formula

=ROW(OFFSET(Sheet1!$A$1,,,360))

What’s happening here is the OFFSET function is returning a range of cells beginning at $A$1, that is 1 column wide and 360 rows tall. i.e. it is returning the cells $A$1:$A$360. Putting the ROW function around this returns an array

of 360 elements, with each element corresponding to a row number in the cells $A$1:$A$360. i.e. the ROW function is returning the array {1,2,3,…,360}. It can happily live in Excel’s memory as a defined name without us needing to enter it into 360 worksheet cells.

Next, we need to convert each value of ‘increment’ from a number of degrees to a number of radians.

We’ll do this by defining a new name ‘t’ and giving it the formula

=increment/360*2*PI()

‘t’ is still an array of 360 elements, but now ranges from 0 to ~6.28.
We then define names for X and Y as follows:

X = SIN(t)
Y = COS(3*t)

Now, save your workbook as ‘ABC.xlsx’. (You can save it as whatever you like, but for this example, we’ll call it ABC.xlsx, and we’ll need to know this later.) All that’s left is to insert a XY-scatter plot chart that refers to the names X and Y for the source data.

Go to the ‘Insert’ tab, the Chart grouping, select a Scatter (X, Y) chart, and pick one of the chart types. If need be, fill in some dummy cells with dummy data and select those cells before inserting the chart, just in case Excel is being picky about inserting a chart when no data is selected.

Once the chart is on the worksheet, click on the chart area (if it is blank) or click on the current chart scatter points (if the chart is not blank) and type this into the formula bar:

=SERIES(“ABC Logo”,ABC.xlsx!x,ABC. xlsx!y,1)

Alternatively, if our sheet is named ‘Sheet1’ we could also type this into the formula bar:
=SERIES(“ABC Logo”,sheet1!x,sheet1!y,1) At which point, after hitting Enter, Excel will change what we typed to the version that uses the workbook name rather than the worksheet name.

The important point is that when entering a defined name as part of the source data for a chart, we cannot type only the name (in this case, only ‘x’ or only ‘y’). Rather, we must type either the workbook name, followed by an exclamation mark, followed by the defined name, OR we must type a worksheet name, followed by an exclamation mark, followed by the defined name. And if our workbook name or worksheet name have spaces or other special characters in them, we will also need to put apostrophes around the workbook/worksheet name, just like what would appear in a normal off-sheet formula reference.

Remembering this is the trickiest part of the entire process. If you remember to always include the worksheet name or workbook name in front of the defined name, then you should be able to adapt this walk-through guide to your own charts with ease.

The other thing we are taking advantage of here is that we can directly edit the components of a chart in the formula bar by editing the SERIES function, rather than by right-clicking the chart, selecting ‘Select Data’ and then editing it through the interface that pops up.

In this instance, the SERIES function is made up of four arguments, and they are:

1. The series title (doubles as the chart title in this case)
2. The x-axis data points
3. The y-axis data points
4. The number of the series that this set of data corresponds to. For charts with multiple data series included in the one chart, this final argument will increment from 1, to 2, to 3 and so on for each data series.

CPD: Actuaries Institute Members can claim two CPD points for every hour of reading articles on Actuaries Digital.