Year 7 · Spring 1
Spreadsheets · Lesson 2 of 5
Formulas and cell references
PracticalTopic 18 Data, spreadsheets and AI
Year 7 · Spreadsheets · Lesson 2 of 5
You will learn to
- Write a formula that starts with = and uses cell references.
- Predict how a relative reference changes when a formula is filled down.
- Use $ to lock a reference so it stays the same when filled down.
Year 7 · Spreadsheets · Lesson 2 of 5
Success criteria
You have got it when you can say:
- I can write =B2*C2 and explain what it does.
- I can predict the formula in row 5 before I fill down.
- I can fix a fill-down mistake by adding $ signs.
Year 7 · Spreadsheets · Lesson 2 of 5
Retrieval starter
Questions from before. Write your answers, then show them.
- Last lesson
Which field makes a good primary key?
A HouseB StudentIDC FirstName
B. StudentID
- Last unit
Why can't an IPv4 part be 300?
A 300 is reservedB Routers can't count that highC Each part is one byte: max 255
C. Each part is one byte: max 255
- Earlier this year
Logging into a friend's account 'just to look' is…
A Against the Computer Misuse ActB Fine if you don't change anything
A. Against the Computer Misuse Act
Year 7 · Spreadsheets · Lesson 2 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 2 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
Spreadsheets: the grid
A spreadsheet is a grid of cells. Columns are letters, rows are numbers. B3 = column B, row 3.
The explanation · step 2 of 3 · part 1 of 2
Formulas and functions
A formula always starts with =. It updates by itself when other cells change.
| Formula | Does | Spanish Excel |
|---|---|---|
| =B2+C2 | Adds two cells | same |
| =SUM(B2:B10) | Adds B2 to B10 | SUMA |
| =AVERAGE(B2:B10) | Mean | PROMEDIO |
| =MAX(...) / =MIN(...) | Biggest / smallest | MAX / MIN |
| =COUNTIF(B2:B10,">=25") | How many meet a condition | CONTAR.SI |
| =IF(B2>=25,"Pass","Retry") | Selection, like if/else | SI |
The explanation · step 2 of 3 · part 2 of 2
Formulas and functions (continued)
Spanish settings use semicolons: =SI(B2>=25;"Aprobado";"Repetir"). Spain uses the comma as its decimal point.
The explanation · step 3 of 3
Relative and absolute references
- =B2*C2 dragged down becomes =B3*C3. That's a relative reference.
- But if every row needs F1, dragging =B2*F1 gives =B3*F2. Wrong.
- Write =B2*$F$1. The $ signs lock it. That's an absolute reference.
Year 7 · Spreadsheets · Lesson 2 of 5
Quick quiz · 1 of 3
To show change over time, use a…
- A Line chart
- B Pie chart
- C Scatter graph
A. Line chart
A line joins the points in time order, so you see the trend.
Year 7 · Spreadsheets · Lesson 2 of 5
Quick quiz · 2 of 3
SELECT FirstName FROM Students WHERE Year = 8; returns…
- A Every field of every student
- B Only the first names of Year 8 students
- C The number of Year 8 students
B. Only the first names of Year 8 students
WHERE keeps the Year 8 records. SELECT keeps only the FirstName field.
Year 7 · Spreadsheets · Lesson 2 of 5
Quick quiz · 3 of 3
A face-recognition model works badly on faces unlike its training photos. This is…
- A A syntax error
- B Overflow
- C Bias from unrepresentative training data
C. Bias from unrepresentative training data
The model only learns what it is shown. Missing groups in the data means worse results for those groups.
Year 7 · Spreadsheets · Lesson 2 of 5
Exit ticket
Answer on paper before you go. Then check.
Every spreadsheet formula starts with…
A $B #C =
C. = The = tells the spreadsheet: calculate this, don't just show it.
=B2*C2 in D2 is filled down to D5. What is in D5?
A =E2*F2B =B5*C5C =B2*C2
B. =B5*C5 Relative references move with the formula: 3 rows down, so 2 becomes 5.