Topic 10 - Spreadsheets (continued)

Topic 10 - Spreadsheets (continued)

1. When a formula is copied by ‘filling down’ the cell references in cells are adjusted as you drag the pointer down the page.

2. Relative cell references: If a formula is copied by filling down, the cell references are adjusted as you drag the replication pointer down the page. This is because it uses the relative address of the cells based on their position in the worksheet.

3. Relative cell references is when a cell is used in a formula and the formula is copied to a new cell reference (address) , the cell references (cell address) changes to take account of the formula’s new position.

4. Absolute cell references: Some we need to use part of a formula that does not change. For example is VAT was recorded in B2 as 20% then the B2 part of the formula must not change because the rate will be 20% for everything. All the other cell ranges will change. When the formula is copied down, the relative address changes but the absolute doesn’t.

5. These use the $ character in front of the letter and number that are required to stay the same. Think of it as the $ as glue. It glues the absolute cell into every formula it is needed in.

6. Changing the data in a spreadsheet model allows you to make and test predictions. You may explore what would happen to your profit if VAT changed to 30%. These are known as what if calculations. They allow you to use a spreadsheet to see what would happen if certain figures changed in a spreadsheet.

7. Benefits of using a spreadsheet:

   • Automatic recalculation: Every time the content of a cell is changed, calculations are performed and other cells are updated if necessary.
     • Accurate calculations: Providing the formulas have been set up correctly, the formulas will always produce a correct calculation
       • Graphs/Charts: Can be drawn using data in selected cells.
         • Replication: Once the first formula is correctly entered, it is possible to drag across to adjacent cells in a row or column so the computer copies the pattern of the calculation.
           • What if calculations
             • Formulas can be reused and stored
               • Calculations involving large numbers are possible