Year 7 · Spring 1

Spreadsheets · Lesson 3 of 5

Functions: SUM, AVERAGE, MAX, MIN, COUNTIF

PracticalTopic 18 Data, spreadsheets and AI

Year 7 · Spreadsheets · Lesson 3 of 5

You will learn to

  • Use SUM, AVERAGE, MAX and MIN on a range of cells.
  • Use COUNTIF to count how many values meet a condition, like 'more than 10'.
  • Work out a mean by hand and check it with AVERAGE.

Year 7 · Spreadsheets · Lesson 3 of 5

Success criteria

You have got it when you can say:

  • I can find the total, the mean, the best and the worst score.
  • I can count how many pupils scored more than 10 with COUNTIF.
  • I can say which function answers which question.

Year 7 · Spreadsheets · Lesson 3 of 5

Retrieval starter

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

  1. Last lesson

    Every spreadsheet formula starts with…

    A $B =C #

    B. =

  2. Last unit

    Which protocol checks every packet arrived and asks again for missing ones?

    A IPB FTPC TCP

    C. TCP

  3. Earlier this year

    The best defence against SQL injection is…

    A A longer passwordB A firewallC Validation and parameterised queries

    C. Validation and parameterised queries

Year 7 · Spreadsheets · Lesson 3 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 3 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 2 · part 1 of 2

Formulas and functions

★Remember

A formula always starts with =. It updates by itself when other cells change.

FormulaDoesSpanish Excel
=B2+C2Adds two cellssame
=SUM(B2:B10)Adds B2 to B10SUMA
=AVERAGE(B2:B10)MeanPROMEDIO
=MAX(...) / =MIN(...)Biggest / smallestMAX / MIN
=COUNTIF(B2:B10,">=25")How many meet a conditionCONTAR.SI
=IF(B2>=25,"Pass","Retry")Selection, like if/elseSI

The explanation · step 1 of 2 · part 2 of 2

Formulas and functions (continued)

!Watch out

Spanish settings use semicolons: =SI(B2>=25;"Aprobado";"Repetir"). Spain uses the comma as its decimal point.

The explanation · step 2 of 2

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 2 of 2

The maths: The mean (average)

Year 7 · Spreadsheets · Lesson 3 of 5

Quick quiz · 1 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")

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

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

Year 7 · Spreadsheets · Lesson 3 of 5

Quick quiz · 2 of 3

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

  1. A 9
  2. B 4
  3. C 6

B. 4

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

Year 7 · Spreadsheets · Lesson 3 of 5

Quick quiz · 3 of 3

Which field makes a good primary key?

  1. A FirstName
  2. B House
  3. C StudentID

C. StudentID

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

Year 7 · Spreadsheets · Lesson 3 of 5

Exit ticket

Answer on paper before you go. Then check.

  1. To show change over time, use a…

    A Scatter graphB Line chartC Pie chart

    B. Line chart A line joins the points in time order, so you see the trend.

  2. SELECT FirstName FROM Students WHERE Year = 8; returns…

    A Only the first names of Year 8 studentsB The number of Year 8 studentsC Every field of every student

    A. Only the first names of Year 8 students WHERE keeps the Year 8 records. SELECT keeps only the FirstName field.