Range Names and References

Specifying function arguments and the case for named ranges
Home 

When specifying the arguments to a function, there are broadly two types of argument:

  • Fixed (Absolute) Arguments. This is information you want to be the same, even if you copy the formula containing the function over to other cells. The key assumptions of a projection function, like the AnnualRates for example, will usually be fixed arguments.
  • Moving (Relative) Arguments. This is information that refers to cells that you want to move in the same way as the function moves. For example, if you copy the function from cell A1 to cell A2, you want a reference in the formula to change from E1 to E2. You usually make Time a moving Argument.
If you input data by reference, i.e. referring to other cells, Excel's default is to offer up a relative reference. If you want this reference to be fixed, and if you are adopting repeating formulae as you will most of the time, you should 'double dollar' this reference, changing it to $E$1. Pressing function key F4 immediately and repeatedly after you have input the reference gets Excel to cycle through the various options for you.

A better method to absolute referencing through double-dollaring is to give the source range a range name such as 'Rents' or 'InterestRates'. Your formula will be much easier to read, and thereafter Excel will automatically offer up the rangename, rather than the reference, if you pick on that range when editing a cell's formula. Note that a range name can refer to a single cell or a range of cells, on any single sheet.

In BusinessFunctions most ranges are 1 dimensional because they are lists of things.

Tip:When using Business Functions, try to use the Business Functions name for your variable, for example GrowthDates, GrowthRates. You won't be able to do this all the time, but when you do it will make it easier when you paste functions from the Quick reference or Wizard, because the function template will already contain the names and are likely to work with the minimum of editing.