1 of 33

Lesson 5: Level up your data skills

Year 7 – Modelling Data – Spreadsheets

2 of 33

A data master’s warm-up routine

Starter activity

2

3 of 33

What symbol must you use at the beginning of a cell when you want to use a function or a formula in that cell?

​

Starter activity

3

4 of 33

What symbol must you use at the beginning of a cell when you want to use a function or a formula in that cell?

​

Equals

=

​

​

Starter activity

4

5 of 33

Name a function that tells you the:

​

Maximum value

​

Starter activity

5

6 of 33

Name a function that tells you the:

​

Maximum value MAX

​

Starter activity

6

7 of 33

Name a function that tells you the:

​

Maximum value MAX

Minimum value

​

Starter activity

7

8 of 33

Name a function that tells you the:

​

Maximum value MAX

Minimum value MIN

​

Starter activity

8

9 of 33

Name a function that tells you the:

​

Maximum value MAX

Minimum value MIN

Number of non-blank cells

​

Starter activity

9

10 of 33

Name a function that tells you the:

​

Maximum value MAX

Minimum value MIN

Number of non-blank cells COUNTA

​

10

11 of 33

Show two ways in which you can add values together in a spreadsheet!

​

Starter activity

11

12 of 33

Show two ways you can add values together in a spreadsheet!

​

  • Formula with plus

= __ + __

​

  • SUM Function

​

​

Starter activity

12

13 of 33

Give the cell references for each of the following ranges:

​

The first ten cells of column B

​

Starter activity

13

14 of 33

Give the cell references for each of the following ranges:

​

The first ten cells of column B

​

B1:B10

​

​

Starter activity

14

15 of 33

Give the cell references for each of the following ranges:

​

The cells in rows 15–20 in column A

​

​

​

Starter activity

15

16 of 33

Give the cell references for each of the following ranges:

​

The cells in rows 15–20 in column A

​

A15:A20

​

Starter activity

16

17 of 33

Give the cell references for each of the following ranges:

​

The cells in rows 15–20 in column A, B and C

​

Starter activity

17

18 of 33

Give the cell references for each of the following ranges:

​

The cells in rows 15-20 in column A, B and C

​

A15:C20

Starter activity

18

19 of 33

Give the cell references for each of the following ranges:

​

All of the cells of column A from row 2 to the end

​

​

Starter activity

19

20 of 33

Give the cell references for each of the following ranges:

​

All of the cells of column A from row 2 to the end

​

A2:A

Starter activity

20

21 of 33

Lesson 5 – Level up your data skills

Objectives

In this lesson you will:

  • Analyse data
  • Use a spreadsheet to sort and filter data
  • Use the functions COUNTIF, AVERAGE, and IF in a spreadsheet

​

21

22 of 33

Sort everything!

Activity 1

  • Select a range
    • The whole sheet
    • Whole columns

​

  • Choose sort options
    • Keep your headers on top!

​

22

23 of 33

Filters

Activity 1

  • A filter helps you see only what you want to see by hiding everything else.

​

23

24 of 33

More functions

Activity 1

24

25 of 33

The AVERAGE function

Activity 1

Superpower: calculate averages in an instant

​

​

25

26 of 33

The AVERAGE function

Activity 1

Superpower: calculate averages in an instant

=AVERAGE(B2:B15)

​

​

​

26

27 of 33

The COUNTIF function

Activity 1

Superpower: count how many cells meet your criteria

27

28 of 33

The COUNTIF function

Activity 1

Superpower: count how many cells meet your criteria

=COUNTIF(B2:B15, “<10”)

=COUNTIF(B2:B15, “Blue”)

​

28

29 of 33

The IF function

Activity 1

Superpower: have a cell show different things depending on a criterion

=IF(C2>10, “Hooray!”, “Too low”)

​

29

30 of 33

Over to you

Activity 2

30

31 of 33

Spreadsheets review

Plenary

31

32 of 33

Homework: Plastic cleanup report

Homework

On the homework revision sheet, fill in the correct formula in the spaces provided as if you were filling in cells in a real spreadsheet.

32

33 of 33

Next lesson

Summary

In this lesson you…

Analysed data

Used a spreadsheet to sort and filter data.

Used the functions COUNTIF, AVERAGE, and IF in a spreadsheet

​

​

Next lesson you will…

Use conditional formatting in a spreadsheet

Apply all the spreadsheet skills covered in this unit

​

33