Absolute and Relative Cell Addressing
How to Use Cell Referencing in Microsoft Excel and OpenOffice Calc
Aug 15, 2008
A spreadsheet (such as Microsoft Excel or OpenOffice.org Calc) is invaluable in the home or in the office - whether it be for calculating mortgage repayment changes due to changes in interest rates, or working out the running costs for a vehicle.
All of the calculations are accomplished by using spreadsheet formulae - formulae which can reference one or more cells in the Excel or Calc sheet using a technique known as spreadsheet cell addressing - an important tool because it allows the user to enter a cell reference into a formula instead of a fixed value.
Complex Excel and Calc Formulae
One complex formula that affects most people is the the calculation of the monthly repayments for the mortgage, for instance, one way to work out the montly repayments is to use:
=a*(i/100/12*POWER(1+i/100/12,y*12))/(POWER(1+i/100/12,y*12)-1)
where:
- a is the amount owed
- i is the annual rate of inflation
- y is the number of years that the mortgage is to run for
so if:
- a = $120,000
- i = 5%
- y = 25 years
the formula becomes:
=120000*(5/100/12*POWER(1+5/100/12,25*12))/(POWER(1+5/100/12,25*12)-1)
Note: At this point users will notice an important difference between Microsoft Excel and OpenOffice.org Calc:
- Excel uses commas as separators in functions, for example - POWER(2,4)
- Calc uses semicolons as separators in functions, for example - POWER(2;4)
Now, this is a very useful formula (and calculates that $120,000 paid at 5% interest over 25 years is $701.51 per month), however there are some drawbacks to the it:
- it is fixed to just one initial amount, one interest value and one repayment period
- the interest value has to be entered 3 times
- the repayment period has to be entered twice
The user can, fortunately, make the formula easier to work with by adding cell addressing, and this addressing can be one of two kinds:
- relative cell addressing
- absolute cell addressing
Using Relative Addressing in an Excel or Calc Spreadsheet
The monthly mortgage calculator can be made more manageable by doing the following:
- enter the value of the house (e.g. 120000) into cell A1
- enter the annual interest (e.g. 5) into cell B1
- enter the period (e.g. 25) into cell C1
Cell addressing allows the user to enter cell references into a formula instead of fixed values, and so the formula (entered into D1) now becomes:
=A1*(B1/100/12*POWER(1+B1/100/12,C1*12))/(POWER(1+B1/100/12,C1*12)-1)
The great advantage to the user is that if they want so see the effect of a different interest rate (e.g. 5.6%) then they just need to enter the value into B1 - and D1 will automatically show the new monthly repayment value ($744.09)
Using Absolute Addressing in an Excel or Calc Spreadsheet
A natural progression at this point is for the user to wish to see a set of results for changing interest rates (say from 5% to 10%) and to see this in a column. Of course they can copy the formula from D1 and paste it into D2, in which case the new formula will appear as:
=A2*(B2/100/12*POWER(1+B2/100/12,C2*12))/(POWER(1+B2/100/12,C2*12)-1)
and then the user will have to enter the values for the total amount for the total amount into A2, the new interest into B2 and the repayment period into C2. However, it's actually only the interest value that the user wants to change; and that's where absolute addressing comes in - absolute addressing fixes the reference to a set column and row rather than updating when the formula is pasted into a new cell; and it's the $ sign that does this for the user:
- $A$1 fixes a reference to cell A1
- $A1 fixes a reference to column A
- A$1 fixes a reference to row 1
So the formula for D1 would now become:
=$A$1*($B1/100/12*POWER(1+$B1/100/12,$C$1*12))/(POWER(1+$B1/100/12,$C$1*12)-1)
When copied into D2 this would become:
=$A$1*($B2/100/12*POWER(1+$B2/100/12,$C$1*12))/(POWER(1+$B2/100/12,$C$1*12)-1)
The user now only has to enter a new interest value into B2 for the formula to work.
 |
 |
 |
