Year 7 · Spring 1

Spreadsheets · Lesson 1 of 5

Cells, ranges and data types

PracticalTopic 18 Data, spreadsheets and AI

Year 7 · Spreadsheets · Lesson 1 of 5

You will learn to

  • Identify a cell by its reference, like B3, and a range, like B2:B6.
  • Explain the difference between text, number and date data.
  • Explain why 'twelve' typed as a word cannot be added up.
  • Enter a class list with the right type of data in each column.

Year 7 · Spreadsheets · Lesson 1 of 5

Success criteria

You have got it when you can say:

  • I can find any cell when I am given its reference.
  • I can say what type of data each column holds.
  • I can enter data so that the numbers can be added up.

Year 7 · Spreadsheets · Lesson 1 of 5

Retrieval starter

Questions from before. Write your answers, then show them.

  1. Last unit

    404 means…

    A Not foundB OKC The server crashed

    A. Not found

  2. Last unit

    Which turns www.bbc.co.uk into an IP address?

    A DNSB HTTPC SMTP

    A. DNS

  3. Earlier this year

    Copyright in the UK…

    A Must be registeredB Is automatic

    B. Is automatic

Year 7 · Spreadsheets · Lesson 1 of 5

Key words (1 of 2)

Formula
A spreadsheet calculation. Always starts with =.
Function
A subroutine that returns a value.
Record / field
One row in a database table: everything stored about one thing.
Primary key
A field whose value is unique for every record.

Year 7 · Spreadsheets · Lesson 1 of 5

Key words (2 of 2)

SQL
Structured Query Language: asks databases questions.
Machine learning
The computer finds rules itself from labelled examples.

The explanation · step 1 of 3

Data vs information

  • Data: 38, 41, 37, 39. Meaningless alone.
  • Information: a patient's temperature in °C over four hours; 41 is dangerously high.

The explanation · step 2 of 3

Spreadsheets: the grid

A spreadsheet is a grid of cells. Columns are letters, rows are numbers. B3 = column B, row 3.

The explanation · step 3 of 3

The data science cycle

  1. Ask a question.
  2. Collect data.
  3. Clean it: duplicates, typos, impossible values.
  4. Analyse: averages, sorting, charts.
  5. Conclude, honestly.
!Watch out

A link between two things (correlation) doesn't prove one causes the other.

The explanation · step 3 of 3

The maths: The mean (average)

Year 7 · Spreadsheets · Lesson 1 of 5

Try it: A tiny spreadsheet

Year 7 · Spreadsheets · Lesson 1 of 5

Quick quiz · 1 of 3

Every spreadsheet formula starts with…

  1. A #
  2. B $
  3. C =

C. =

The = tells the spreadsheet: calculate this, don't just show it.

Year 7 · Spreadsheets · Lesson 1 of 5

Quick quiz · 2 of 3

=B2*C2 in D2 is filled down to D5. What is in D5?

  1. A =E2*F2
  2. B =B2*C2
  3. C =B5*C5

C. =B5*C5

Relative references move with the formula: 3 rows down, so 2 becomes 5.

Year 7 · Spreadsheets · Lesson 1 of 5

Quick quiz · 3 of 3

In Spanish Excel, =IF(B2>=25,"Pass","Retry") becomes…

  1. A =SI(B2>=25;"Aprobado";"Repetir")
  2. B =IF(B2>=25;"Pass";"Retry")
  3. C =SI(B2>=25,"Aprobado","Repetir")

A. =SI(B2>=25;"Aprobado";"Repetir")

Spanish name SI, and semicolons, because the comma is the decimal point.

Year 7 · Spreadsheets · Lesson 1 of 5

Exit ticket

Answer on paper before you go. Then check.

  1. The numbers 3, 9, 4, 4, 10. What is the median?

    A 9B 6C 4

    C. 4 Sort: 3, 4, 4, 9, 10. The middle (3rd) value is 4.

  2. Which field makes a good primary key?

    A StudentIDB HouseC FirstName

    A. StudentID It is unique for every student and doesn't change.