Year 7 · Spring 1 · Spreadsheets

Formulas and cell references

What this lesson covers

Topic 18 Data, spreadsheets and AI

KS3 Computing · Data is raw facts. Information is data with meaning. Spreadsheets calculate, databases store and search, and AI learns patterns from examples.

Managing DataCambridge Lower Secondary Computing 0860

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.

Success criteria

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.

Key words

Say each word, then read what it means. Every one is in the glossary too.

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.
SQL: Structured Query Language: asks databases questions.
Machine learning: The computer finds rules itself from labelled examples.

The explanation

Read it here, or open the study page below to go one step at a time with things to try.

Spreadsheets: the grid

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

Formulas and functions

★Remember

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

!Watch out

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

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.

Study it

Open the study page and go one step at a time.

Topic 18 · Year 7

Data, spreadsheets and AI

Data is raw facts. Information is data with meaning. Spreadsheets calculate, databases store and search, AI learns patterns from examples.

Check yourself

One question at a time. The reason shows after each answer.

Saved on this device.