Design & Audit Tips, Part III   

  Back  Forward  "Design & Audit" Home  Excel Review Home

 

III. Creating Formulas

Numbers in Formulas
Reproducing Formulas
Relative and Absolute Addressing
Simple Formulas
Range Names
Intermediate Quantities
Flow of Calculation

 

Numbers in Formulas

Try to avoid using numbers (actual data) in formulas. Instead use cell references that point to data cells. By keeping a data item in a unique location in the worksheet you can much more easily change the item and have the change affect the use of that item throughout the worksheet.

Example: Cell E5 holds the data item 6%, that represents tax rate.

In all formulas that need to reference tax rate, refer to cell E5 instead of typing in 6%. If the tax rate later changes to 6.5% you can change the rate value one place in your worksheet. The value changes in every formula that refers to that cell and you need not locate and change every formula that uses the value.

Return to Top

 

Reproducing Formulas

You may often need to reproduce a formula in several places in a worksheet. In many cases, you can type in the formula one time and then copy it to the other locations, avoiding hard-to-detect typing errors. When copying formulas, be sure to understand how to use relative and absolute addressing, described below.

Return to Top

 

Relative & Absolute Addressing

For copied formulas, Excel uses relative addressing as the default. That is, Excel adjusts the cell references in your source formula when the formula is copied to new locations.

The worksheet above shows three formulas in cells D4, D5, and D6. Because of this model's layout, there's no need to type in three formulas. Type in just the first formula in cell D4 and then copy it to cells D5 and D6.

This view of the same worksheet shows the contents of the formula cells. You can see that for each copied formula, Excel adjusted the cell references to refer to the data in the correct row.

However, there may be occasions when you want to override Excel's relative addressing default. In the example below, the formula in the Tax column makes use of the values in column D and the 5% value in cell B1.

The illustration below shows how the formulas appear. In order to properly copy the formula in E4 to cells E5 and E6, absolute addressing must be specified in the master formula in cell E4 for the 5% tax figure.

An absolute cell reference is indicated by putting a dollar sign before the row and column reference. In the example worksheet above, cell B1 holds the tax value. When the cell reference is entered as $B$1 in a formula it indicates an absolute references to that cell. As an absolute reference, the reference won't change when the formula containing the reference is copied.

So when the master tax formula in cell E4 is copied down the column to cells E5 and E6, the reference to the tax rate remains correct, pointing to cell B1. Note, however, that the first cell references in the master tax formula (D4) is left relative. When it's copied in a formula, it becomes D5 and D6, which is also correct.

Return to Top

 

Simple Formulas

Keep formulas simple and straightforward. This makes them easier to read and to debug. If you need to exress complex relationships, create several formulas and use them like building blocks instead of constructing an overly long formula.

Return to Top

 

Range Names

Use range names instead of cell references to make formulas easy to understand. (See the discussion above on naming and applying ranges.) If you assign a name to a cell or a range after you've used that cell or range in a formula, use the Insert, Name, Apply commands to have Excel use the name in formulas place of a cell reference. If you want the name to be used in place of any reference to that cell anywhere it might appear in the worksheet, select a single cell before issuing the commands. If you want the name applied to only a particular range, select that range before issuing the commands.

Example:

Formula without range names applied: =C10*A3

Formula with range names applied: =Subtotal*Tax

Return to Top

 

Intermediate Quantities

Arrange your model to show intermediate quantities of interest, such as subtotals.

Return to Top

 

Flow of Calculation

Try to plan the arrangement of worksheet formulas and labels so the flow of calculations is obvious.

Return to Top