Topic 10 - Spreadsheets

Topic 10 - Spreadsheets

1. Formulas and functions

   • Formulas
     • Must start with an =
     • Formulas perform calculations on cell contents
     • Formulas appear in the formula bar
     • Formula results are automatic recalculated when data changes
     • The four main mathematical calculations are: + add, - subtract, * multiply and / divide
   • Functions
     • Must start with an =
     • Functions are part of the spreadsheet software. They are specialised calculations built in the spreadsheet software
     • The functions are SUM, COUNT, AVERAGE, MIN, MAX and IF

2. Cells

   • A cell range always starts with a letter and ends with a number
   • A range of cells in a row or column
   • Data entered into cells can be different data types; currency, number, real, integer, date and text
   • Each cell can contain:
     • Labels - any text describing the data in a row or column
     • Data -any values that are entered for the use of calculations
     • Formulas and functions - mathematical calculations carried out automatically

3. Benefits of spreadsheets

   • Automatic recalulation
   • Accurate calculations
   • Graphs/Charts
   • Replication
   • What if calculations
   • Formulas can be reused and stored
   • Calculations involving large numbers are possible

4. Cell referencing

   • Absolute cell referencing
     • Absolute cell referencing is where a reference to a cell used in a formula where, when the formula is copied to a new cell reference, the cell reference does not change. It is glued in place by the $ character
   • Relative cell referencing
     • Relative cell references are when a cell is used in a formula and the formula is copied to new cell reference, the cell references changes to take account of the formula's new position
   • When a formula is copied by 'filling down' the cell references in cells are adjusted as you drag the pointer down the page

5. Graphs & charts

   • Specific data can be selected on a spreadsheet and a chart/graph of this data can created
     • Bar charts - to compare values
     • Pie charts - to show how a total is divided among a number of different categories
     • Line graph - display trends
   • Using graphs and charts allows us to notice patterns and trends easily

6. Formatting

   • The content of a cell can be formatted
     • Font style, size, colour, border style, alignment, cell colour
     • Multiple cells can be merged into one cell

7. What if calculations

   • Changing the data in a spreadsheet model allows you to make a test predictions. 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