Spreadsheet worksheet 3 Absolute cell references
Using the saved spreadsheet ‘Currency conversion’ Access the site to find out the most up to date currencies… http://newsvote.bbc.co.uk/1/shared/fds/hi/business/market_data/currency/default.stm Calculate the following currency conversions using absolute cell references. e.g. $A$1*C1 Use the empty boxes at the bottom of the table to choose two of your own foreign currencies. Currency Exchange rates Calculate the foreign currency you will receive if you exchange the following amounts of £s US Dollars £5, £10, £15 Japanese Yen £3, £6, £135 Malaysian Ringgit £30, £23, £56 Swiss Franc £44, £32, £12 £22, £25, £45 £21, £39, £88 Use the layout of the ‘Currency conversion’ Excel spreadsheet as a basis for planning and devising your own worksheet. Make sure that you write absolute formulas.
An absolute cell reference is one which does not change when it is copied to other cells. They are identifiable by the use of the $ sign i.e. $A$2. If a formula contains an absolute reference then it will always refer back to the same cell.
Relative cell references
Relative cell references are used when you are copying a formula from one cell to other cells. They allow for the cell references to change to replicate and apply the same formula. Create a new worksheet called multiplication tables. In column A number the cells 1 – 12 In column B copy the x symbol in cells B1-B12 In column C copy the number 12 in cells C1 –C12 In cell D1 create a formula to multiply cells A1 and C1 Copy the formula and paste into the remainder of the column. Look at the change in cell references. The formula will use relative references which allow the row reference to change. Task: Create a worksheet that shows the multiplication table for the 36, 45 and 342 times tables. You will need to copy and paste the formula as appropriate. Save your work as ‘Tables1’