Spreadsheets and Data Modelling
Formulas, functions, absolute and relative references, conditional logic and charts. Spreadsheets are the most widely used programming tool on Earth.
Formulas and functions
Every formula starts with an equals sign. Without it, the spreadsheet treats what you type as text.
=B2+C2
=B2*0.2
=(B2+C2+D2)/3Cell references
Referring to a cell rather than typing a number means the formula updates automatically when the data changes. This is the single most important idea in spreadsheets.
Bad: =45*0.2 Good: =B2*0.2
Common functions
| Function | What it does | Example |
|---|---|---|
SUM | Adds a range | =SUM(B2:B20) |
AVERAGE | Mean of a range | =AVERAGE(B2:B20) |
MAX | Largest value | =MAX(B2:B20) |
MIN | Smallest value | =MIN(B2:B20) |
COUNT | How many cells contain numbers | =COUNT(B2:B20) |
COUNTIF | How many meet a condition | =COUNTIF(B2:B20,">50") |
ROUND | Rounds to a number of decimals | =ROUND(B2,2) |
IF | Chooses between two results | =IF(B2>=50,"Pass","Fail") |
The IF function
=IF(condition, value if true, value if false)
=IF(B2>=50,"Pass","Fail")
=IF(C2>100,"Over budget","Within budget")IFs can be nested for more than two outcomes:
=IF(B2>=70,"Merit",IF(B2>=50,"Pass","Fail"))- The formula bar shows what is in the selected cell. Every formula starts with an equals sign.
- A cell is named by its column letter and row number, so this is cell B6.
- The range B2:B5 is a block of cells, from B2 down to B5. SUM adds them up.
Absolute and relative references
This is the idea that separates people who fight with spreadsheets from people who use them properly.
Relative references
B2 is relative. Copy the formula down one row and it becomes B3. Copy it right one column and it becomes C2.
This is usually what you want. A column of totals copied down should refer to each row in turn.
Absolute references
$B$2 is absolute. The dollar signs lock it, so copying the formula never changes it.
Use this when every row must refer to the same cell, such as a VAT rate stored once at the top of the sheet.
=B2*$E$1 copied down becomes =B3*$E$1, =B4*$E$1
=B2*E1 copied down becomes =B3*E2, =B4*E3 which is wrongMixed references
$B2locks the column but not the rowB$2locks the row but not the column
If a formula works in the first row and produces nonsense when copied down, you almost certainly needed dollar signs on a reference that should not have moved.
Step 1 of 4 One cell holds the VAT multiplier. The formula points at a price and at that cell.
Modelling, charts and good design
What a model is
A spreadsheet model represents a real situation so you can ask what if questions.
Examples: a household budget, the cost of running a school trip, projected profits for a business, the population of an island over ten years.
The point of a model is that you change one input and every dependent figure updates instantly. Working out the effect of a 10 per cent price rise takes one keystroke rather than an hour with a calculator.
Choosing a chart
| Chart | Best for |
|---|---|
| Bar or column | Comparing separate categories |
| Line | Showing change over time |
| Pie | Showing parts of one whole, with only a few categories |
| Scatter | Showing whether two things are related |
Every chart needs a title, axis labels and, where there is more than one series, a legend. A chart without labels communicates nothing.
The slices become impossible to compare. Pie charts work with three or four segments at most.
Designing a good spreadsheet
- Put inputs together at the top or on a separate sheet, clearly labelled
- Never bury a number inside a formula. Put it in a cell and reference it.
- Use headings, borders and shading so the structure is visible
- Format currency, percentages and dates properly
- Use conditional formatting to highlight important values automatically
- Freeze the header row so it stays visible while scrolling
- Use data validation to restrict what can be typed into a cell
- Bar chart: compare separate categories.
- Line chart: show how something changes over time.
- Pie chart: show parts of one whole, with only a few categories.
- Scatter graph: show whether two things are related.
Key terms you must be able to define
- Formula
- A calculation in a cell, always beginning with an equals sign.
- Function
- A built in operation such as SUM or AVERAGE that performs a common calculation.
- Cell reference
- The address of a cell, such as B2, used in a formula.
- Relative reference
- A reference that changes when the formula is copied to another cell.
- Absolute reference
- A reference locked with dollar signs so it does not change when copied.
- Range
- A block of cells, written as the top left and bottom right separated by a colon.
- Model
- A spreadsheet that represents a real situation so that what if questions can be asked.
- Conditional formatting
- Automatically changing the appearance of a cell based on its value.
- Data validation
- Restricting what can be entered into a cell to prevent invalid data.
Flashcards
Say the definition out loud before you flip the card. Retrieving it from memory is what builds recall, reading it again is not.
Reach the top grade on this topic
The top level here comes from explaining why a spreadsheet is the right tool, not just using one.
Explain the power of cell references. A model built on cell references updates every dependent figure the instant an input changes, which is the entire reason spreadsheets exist. Typing numbers into formulas throws that away.
Justify your chart choice. "A line chart because the data shows change over time, and the trend is what matters more than the individual values."
Explain absolute references properly. Say what would go wrong without them: the reference would shift down with each row and point at an empty or wrong cell.
- Write an IF formula with a sensible condition and two outcomes
- Explain when to use an absolute reference and what goes wrong without one
- Choose and justify a chart type for a given set of data
- Design a model with clearly separated inputs and calculations
Mistakes that cost marks here
- Forgetting the equals sign, so the spreadsheet stores the formula as text.
- Typing values directly into formulas instead of referencing a cell.
- Copying a formula down without an absolute reference, so a fixed value slides down the column.
- Using a pie chart for data that is not parts of one whole.
- Producing a chart with no title and no axis labels.
A spreadsheet is a program. Every formula is a small piece of code, and a spreadsheet with logic in it is a working computer model. The first one, VisiCalc, sold so many Apple II computers that it is credited with creating the personal computer market.
Knowledge check
=SUM(B2:B10) do?=B2*E1. What happens when it is copied down?=COUNTIF(B2:B20,">50") do?Exam-style questions
Five written questions in the style of the real paper. Write a full answer, then let the marker check it against the mark scheme.
- LA formula must begin with an equals sign
- LCorrect example such as =B2+C2
Every formula must begin with an equals sign, which is what tells the spreadsheet that the contents of the cell should be calculated rather than treated as ordinary text. An example that adds cells B2 and C2 together is =B2+C2.
- LA relative reference changes when the formula is copied
- LAn absolute reference stays the same because it is locked with dollar signs
- LGives an example such as a VAT rate or a fixed price stored in one cell
- LExplains that without the dollar signs the reference would move and give wrong results
A relative reference such as B2 adjusts automatically when a formula is copied, so copying a formula down one row changes B2 to B3. This is usually what you want, because each row of a calculation should refer to its own data. An absolute reference such as $E$1 is locked by the dollar signs, so it stays pointing at exactly the same cell no matter where the formula is copied. An absolute reference is needed when every row must refer to the same single value, for example a VAT rate stored once in cell E1. If the formula were written as =B2*E1 and copied down, the reference would slide to E2, then E3, which are empty cells, so every result after the first row would be wrong.
- LUses a nested IF function
- LTests the highest condition first
- LCorrect outcomes for all three cases
=IF(B2>=70,"Merit",IF(B2>=50,"Pass","Fail")) The outer IF tests the most restrictive condition first, so a mark of 85 is correctly given Merit. Only if that condition is false does the spreadsheet evaluate the inner IF, which then checks for 50 or above and gives Pass, with Fail as the final alternative. Testing the conditions in the wrong order would mean a mark of 85 was caught by the 50 test and wrongly labelled Pass.
- LInput values are kept in clearly labelled cells rather than inside formulas
- LSo a value can be changed once and every calculation updates
- LCell references are used in formulas rather than typed numbers
- LFormatting such as currency, headings and conditional formatting makes it clear and reduces errors
The first feature is that all the input values, such as the coach hire cost, the entry price per student and the number of students, are kept in their own clearly labelled cells, usually grouped together at the top or on a separate sheet. This means the model can answer what if questions instantly: changing the number of students in one cell updates every dependent calculation automatically, which is the entire point of building a model rather than working the figures out by hand. The second feature is that every formula uses cell references rather than typed numbers, so no value is buried where it cannot be seen or updated. Alongside this, sensible formatting matters: currency values formatted as currency, clear headings and borders so the structure is obvious, and conditional formatting to highlight if the total goes over budget, all of which make the model easier to read and much less likely to be misused.
- LRecommends a line chart
- LThe data shows change over time
- LA line chart makes the trend and any patterns easy to see
A line chart is the most suitable choice. The data consists of one measurement taken at regular intervals across a month, so it is a time series, and a line chart is specifically designed to show how a value changes over time. Joining the points with a line makes any trend immediately visible, so it is easy to see whether temperatures rose through the month, whether there was a cold spell, and how much the values varied day to day. A pie chart would be completely unsuitable because the daily temperatures are not parts of a single whole, and a bar chart, while readable, would make the overall trend harder to follow than a continuous line.