202X2 Formulae & Functions in Spreadsheets

 |  No Comments

The greatest power of spreadsheets is that they can be programmed (the formula) to do the maths as you update the data

The most basic calculations are:

  • add +
  • subtract (minus) –
  • multiply *
  • divide /
VIDEOABOUTCLICK METHOD

https://www.youtube.com/watch?v=xc14gFFyiTw

  • under 4 min
  • Mathematical operators
  • Understanding cell references
  • Creating formulas
  • Copying and editing formulas

Worksheet

Spreadsheet

https://www.youtube.com/watch?v=RgvdCHjOKYg

These symbols are keys on your keyboard (on the main keyboard – you will need SHIFT for the * as it is over the 8). They are also on the calculator keyboard of a desktop computer

  • You can combine + – / * according to the maths you need
  • The answer of the calculation will display instead of the formula
  • You tell the spreadsheet you are about to write a formula by always starting with = (equals)
  • You can build very complex formulae

 

EXSPREADSHEETDOWNLOADIMPORTFUNCTIONSCLASS NOTES

In the SPREADSHEET tab there is a spreadsheet of calculations

  1. recreate this in a new blank spreadsheet
    1. save with an appropriate name
    2. save in your classwork spreadsheet folder (create the folder if necessary)
  2. copy each row separately
    1. replace “formula goes here” with the formula for the calculation to find the answer
    2. repeat for the next row

NOTE ABCD across top and 1234 on the LHS are the column and row names

A B C D E F G H
1 5 plus 7 formulae goes here
2 5 minus 7 formulae goes here
3 5 times 7 formulae goes here
4 5 divided by 7 formulae goes here
5 3 plus 7 then times 2 formulae goes here
6 15 divided by 5 then plus 3 formulae goes here
7 30 minus 50 thats divided by 5 formulae goes here
8 20 plus 30 thats timesed by 2 plus 6 formulae goes here
9 18 plus 12 all divided by 6 plus 12 formulae goes here
10 9 plus 6 all divided by 3 times 6 formulae goes here

 

  • halved means divided by 2
  • quartered means divided by 4

  • Open
  • File > Download (as Excel)
    • complete
    • save/upload these links
  1. Beginning formulae with instructions*
  2. Jaycee’s birthday D(1)
  3. Jaycee’s birthday F(2)

In this exercise you will download a CSV and import it into your spreadsheet software

  1. Download the file from here
    1. Save it in a suitable place
  2. Start MS Excel
  3. File > Open
    1. Select your downloaded file
    2. Check the import options (wizard)
  4. Carry out the calculations as indicated in the file

Now that you have completed the basic formulae

  1. add up all the numbers in column C, put in C11
  2. make a function to add up all the numbers in column H, put in H11
  3. (make up another 10 of your own calculations)
  4. calculate the average of the numbers in column A?
    1. use a simple formula to calculate the average over a range (into A11)
    2. use the function in the presentation to do this the quick way (into A12)
    3. are they the same?
  5. What are the MAX and MIN numbers in column H?
    Create a space under your table to display the answer
  6. change some of the data (numbers) – what happens to the final calculations?


You need to be logged in to view the rest of the content. Please . Not a Member? Join Us