Author: Louise Pryor

Categories
Notes Old site

Using Names for formulae

The usual way to use Names in Excel is to use them to refer to absolute ranges. Typically, the name VATRate might refer to the range $D$2, for example.

However, it is possible to define names to refer to formulae that use relative ranges.

Names that refer to formulae can be useful when they are used to define dynamic ranges. On the whole, however, they should be used with caution: make sure they are documented somewhere, so that a future maintainer will find it easy to understand what is going on. One source of confusion is that their definition may depend on the currently active cell.

Names for formulae

When you define a name using the Define Name dialog, you can actually type any formula you like in the Refers to box. The formula is interpreted as being relative to the currently active cell in the workbook.

For example, if you have cell B9 selected, so that it is currently active, you could define a name AboveAndLeft as referring to =B8+A9. You could then use this name in cell D12, say, to add the
values in D11 and C12.

Picture of formula name

Categories
Notes Old site

Using Names in charts

There are some ways of using Excel names in charts that can help make your charts easier to set up and maintain. You can use names to define the data series; in conjunction with dynamic ranges, you can then build dynamic charts without the use of macros.

Using names in charts

You can use a name in most of the places that you can use a range, when setting up a chart. However, you must always prefix the name with the worksheet or workbook name and an exclamation mark.

In particular, you can use names when specifying data series.

Chart data series

If you are using a name with workbook visibility (ie not local to a worksheet), you can prefix the name with either the workbook name or the sheet name when you type it in. Excel will automatically change the prefix to the workbook name. If you are using a name that is local to a worksheet, you must prefix the name with the worksheet name.

The useful thing about using a name to specify a data series is that if you change the range the name refers to, the chart will automatically show the new data. You don’t have to edit the chart in any way. As editing charts can be fiddly, this can be very helpful.

You cannot use names when specifying a data range. If you type a name in, Excel will automatically change it to the absolute address.

Chart data range

Categories
Notes Old site

Modularity

Modular systems are usually easier to maintain and update, as well as being faster to develop in the first place. So what is modularity?

A modular system is one that consists of a number of small, coherent, chunks (or modules) each of which is self-contained and has a well-defined interface.

Modularity is the single attribute of software that allows a program to be intellectually manageable.
G Myers, 1978

Modularity helps to reduce complexity. A modular system can be understood in terms of the individual modules, without having to grasp all the details at once. Modularity thus provides abstraction.

If the module boundaries are drawn appropriately, modifications to the system are often confined to one or two modules, thus simplifying the modification process.

Another advantage of modularity is that it facilitates code reuse. Again, if the modules are well defined, a single module can be used in several different systems, thus reducing the need to reinvent the wheel.

Categories
Notes Old site

Abstraction

The notion of abstraction is extremely important for writing good programs or designing good systems. Basically, the idea is that you want to avoid getting hung up on the details: you want to separate the things that change from those that
don’t.

In Excel spreadsheets the easiest example of abstraction is the use of names. You can define a name to refer to a particular range: for example, you could define VATrate to refer to cell D3 on sheet TaxCalcs. By using a name for this, you have abstracted away two levels of detail:

  • The actual VAT rate to use; instead you use the value in cell D3 on sheet TaxCalcs.
  • The actual cell that contains the value; instead you use the name VATrate.

Using abstraction usually has advantages for maintainability, and we can see several of them in this example.

  1. A formula that reads =B26*(1+VATrate) is much easier to understand than either =B26*(1+$D$3) or =B26*1.175
  2. If you want to change the rate of VAT that you are assuming, it is much easier to change the value of a single cell than it is to go through all the formulae in the workbook looking for places where 17.5% is used, and changing them all individually.
  3. If you want to change the layout of your sheet, it is much easier to change the definition of the name VATrate than it is to change all references to cell D3, or to make sure that you drag and drop the cell or cut and paste it in such as way as to preserve all the references.

There are other ways of making use of the advantages of abstraction, but the use of names is the most common in Excel.

Categories
Notes Old site

Names can be local to worksheets

Many names in Excel are global in scope: the same name means the same thing wherever it is used. For example, the name
Inflation might refer to cell B2 on worksheet Costs.

Often, though, you may have many similar sheets; for example, an Costs sheet for each year of a 5 year business plan. In such cases you might want to use a different inflation rate for each year, but you would still want to use a named range. The solution is to use a name that is local to the worksheet in question.

Names local to worksheets

You can define a name that is local to a specific worksheet by prefixing the name with the worksheet name followed by an exclamation mark. So, for example, you might define CostsYear1!Inflation to refer to B2 on sheet CostsYear1, CostsYear2!Inflation to refer to B2 on sheet CostsYear2, and so on.

If you use the name Inflation in a formula on sheet CostsYear1, it will refer to the value in cell B2 on that sheet. Similarly, the name Inflation when used in a formula on sheet CostsYear2 will refer to the value for year 2.

You can use a name that is local to a specific worksheet on another sheet by using the full name with the sheet prefix, for example CostsYear1!Inflation.

If you have a local and a global name with the same name, the global name is not visible on the worksheet in which the local name is defined.

The Define Name dialog shows only global names and those that are local to the currently active worksheet. Similarly, the Name Box also omits names that are local to other worksheets.

Categories
Notes Old site

Defining Names

In Excel, you can give a range a name. You can do this in two ways, using the Name Box or by using the Define Name dialog. You can change the range that a name refers to, or delete a name, using the Define Name dialog.

Using the Name Box is quicker and easier for straightforward name definitions, but the Define Name dialog provides a more flexible interface.

Using the Name Box to define names

Picture of Name Box

Select the range that you wish to give a name to, and type the name into the Name Box, which is the box just above the top left of the spreadsheet area. In the example, we are defining the name Total to refer to the single cell
A2. Multi-celled ranges canbe given names in exactly the same way.

The Name Box shows all the names that are currently defined, through the drop-down just to its right. Select any name in the drop down to select that range in the workbook.

If you happen to select a range that has a name defined for it, the name will appear in the Name Box.

Using the Define Name dialog to define names

Open the Define Name dialog from the menu, using Insert\Name\Define... Type the name that you wish to define in the top box (Excel will often suggest one for you) and then use the Refers to box to enter the range. You can type the range in, or select it in the usual way. If you type it in, make sure it is an absolute range (with dollar signs) unless you know what you are doing (see related note on using names for formulae). Finally, use the Add button to add the new name to the list.

To delete a name, select it in the list and use the Delete button.

To change the definition of an existing name, select it in the list and change its definition in the Refers To box, then use the Add button.

Categories
External link Old site

Current consultations at the FSA

This entry, which describes an external link, was imported from my old website which is now defunct. I’ve therefore given it an artificial post date of 1 August 2002.

The FSA maintain a list of current consultation and discussion papers at http://www.fsa.gov.uk/pages/Library/Policy/CP/current/index.shtml.

Categories
External link Old site

Is this spreadsheet a tax evader?

This entry, which describes an external link, was imported from my old website which is now defunct. I’ve therefore given it an artificial post date of 1 August 2002.

This paper, by Ray Butler of H.M. Customs and Excise, summarises the audit experience, describes the methodology and outlines the results to date of a campaign of spreadsheet testing started in July of 1999. Of the seven spreadsheets selected for audit, six contained significant errors. It was published in the proceedings of the 33rd Hawaii International Conference on System Sciences in 2000, and is available at http://www.eusprig.org/hicss33-butler-evader.pdf.

Categories
External link Old site

CP140: The Interim Prudential Sourcebooks for Insurers and Friendly Societies and the Lloyd’s Sourcebook: Guidance on Systems and Controls

This entry, which describes an external link, was imported from my old website which is now defunct. I’ve therefore given it an artificial post date of 1 August 2002.

Consultation paper 140 from the FSA sets out guidance on •high-level controls; • risk assessment; • legal risk; • internal audit; • management information; • outsourcing; and • group risk. This guidance will take effect before the Prudential Sourcebook is implemented in 2004. It is available at http://www.fsa.gov.uk/Pages/Library/Policy/CP/2002/140.shtml. It was issued in July 2002 and the consultation period ended on 3rd October 2002. PS140 is the result of the consultation.

Categories
External link Old site

The firm risk assessment framework

This entry, which describes an external link, was imported from my old website which is now defunct. I’ve therefore given it an artificial post date of 1 August 2002.

This is the fourth document in the Building the new Regulator series of reports issued by the Financial Services Authority. It describes the FSA’s ARROW framework for risk assessment and is essential reading for anyone in a regulated firm who will be involved in the risk assessment process. It was published in February 2003 and is available at http://www.fsa.gov.uk/pubs/policy/bnr_firm-framework.pdf.