LEARNING MICROSOFT EXCEL 2000
Copyright CIT, 1999-2000. All rights reserved.

CHAPTER 2. BUILDING A SIMPLE SPREADSHEET/3

Copying Equations
Consider the problem of working out the cost of buying 5kg of potatoes at 95 cents a kilo, 2kg of kumara at $1.85 a kilo, and 3kg of carrots at $1.05 a kilo.

Step 2.18 You could lay it out in a spreadsheet as follows

Figure 2.16

In A2 to A4 we have put descriptions of the product ordered. This is the third type of data Excel recognises, namely text.

Step 2.19 In D2 we would write 5 times 0.95, but as an equation. Enter =B2*C2 into D2.

Figure 2.17

For kumara you would want =B3*C3 and carrots =B4*C4

But wait…… Excel offers you a short cut to do this. In the highlighted cell the bottom right hand corner is called the fill handle.

Step 2.20 Place the cursor over the fill handle (it changes to :). Drag that down through D3 and D4 and it will copy the equations.

Figure 2.18

Check the equations in D3 and D4. To complete the spreadsheet we would like the total cost in D5. The equation would be =D2+D3+D4.

Step 2.21 A simpler way is to define D2 to D4 (if not already defined) and press the AutoSum button in the toolbar.

Figure 2.19

This generates =SUM(D2:D4). SUM is a function and simply means add up the range specified.

So =SUM(A1:A5) is the same as =A1+A2+A3+A4+A5
and =SUM(B2:D2) is the same as =B2+C2+D2.

Press the function button to see other functions.

Recapping copying across

Fig 2.20

Copying A3 to B3 and C3 will give

Fig 2.21

Recapping copying down

Fig 2.22

Copying C1 to C2 and C3

Figure 2.23

This technique is called relative addressing. Note how the cell addresses have changed to reflect the new row that the formula is in.

Absolute Addressing
As described above, copying an address A1 down will give you A2 and copying an address A1 across will give you B1. This is relative addressing. In some circumstances you do not want the address to change. This is called absolute addressing. To specify absolute addressing for A1 you write $A$1, that is you put a $ sign in front of the row or the column you do not want changed.

For some reason the computer industry does not give retail prices which include GST (currently 12.5%). For the computers with the same specifications IBM charge $3140, Compaq charge $3007 and PC Direct charge $2710. It is required to calculate the GST and total price for each machine.

Step 2.22 Set up the spreadsheet as follows

Figure 2.24

In column C we will put the GST amount. In column D we will put the total cost.

Step 2.23 Enter into C4 the price (B4) times the GST rate (B1) divided by 100, that is =B4*B1/100. Enter into D4 the price and GST =B4+C4.

Figure 2.25

Step 2.24 Now copy the equations in C4 and D4 down 2 cells.

Figure 2.26

You can see that the GST for Compaq and PC Direct are wrong. The equation in C5 is

Figure 2.27

Because we copied it down, B1 got changed to B2. We wanted it to stay at B1. To do this you use B1 absolute address, $B$1.

Step 2.25 Re-enter C4 as =B4*$B$1/100, and recopy down two cells.

Figure 2.28

These values are now correct.

Advice
Generally you will only need to use relative addresses, but occasionally, especially when you want to do ‘what ifs’, absolute addresses are necessary. Make sure you understand relative addressing.

Home | Other Courses | Notes | Feedback