Using Spreadsheets and formulae

1
Using Spreadsheets and formulae

• An explanation

2
What is a spreadsheet

• In the introductory slideshow you were told that
  spreadsheets could be used to do four things
• record data
• Search for particular items
• perform calculations
• produce graphs and charts

3
Formulae

• In Microsoft Excel, you can enter numbers and
  mathematical formulas into cells.
• A formula is used to calculate the value of a
  cell from the contents of other cells

4
Basic Formula

• Formulae must start with an sign
• Calculations are performed according to BOMDAS

5
To create a formulae

• If you would like to create a formulae, click on
  the cell.

6
Equation entry

• This formula is entered in the formula bar in the
  section called the data entry zone
• When entering a mathematical formula, precede the
  formula with an equals sign

7
Addition

• Move the cursor to cell A1.
• Type 1. Press Enter.
• Type 1 in cell A2. Press Enter.
• Type A1A2 in cell A3. Press Enter.
• Note that cell A1 has been added to cell A2 and
  the result is shown in cell A3.

8
Subtraction

• Look at the diagram alongside and see if you can
  work out the process

9
Multiplication

• Likewise multiplication.
• The user types into the data entry zone of the
  formula bar
• Then multiplies the cell references by each other

10
Replication

• To help you speed up the process, you can repeat
  this formula
• Simply click on the black square on the bottom
  right corner and drag.
• The equation will be repeated

11
Using Brackets

• Brackets can be used to change the order of
  calculation.
• Calculations start inside the innermost pair of
  brackets and work outwards
• Every opening bracket must have a closing bracket
• You can nest up to seven pairs of brackets

12
Example of Brackets

• For example
• ((100-(A81)5)0.5)/10
• If the value of A8 is 7
• ((100-(71)5)0.5)/10
• ((100-85)0.5)/10
• ((100-40)0.5)/10
• (600.5)/10
• (60.5)/10
• 6.05

13
Cell References

• Formula can use both numbers and cell refrences
• B10C10

14
Relative cell references

• When you copy formula to another cell, any cell
  references are automatically updated so that the
  refer to the cell in the same relative position
• Take the formula found in F3
• F3 (C3D3E3)
• Copy it to F4
• F4( C4D4E4)

15
Absolute Cell References

• If you want the formula to refer to the same
  cell, regardless of where the formula is copied
  use absolute references
• Put a sign in front of the cells co-ordinates
• B12 - Relative cell reference
• B12 - Absolute cell reference

16
The End

• Well done, now complete off the worksheet and try
  your hand at formulae.