Teacher’s notes included in the Notes Page Flash activity. These activities are not editable.
Web addresses
Icons key: For more detailed instructions, see the Getting Started presentation
Modelling with Spreadsheets
Learning objectives
Learn how to format data labels and
numeric data.
Understand how formulae and
functions help simplify a spreadsheet.
Be able to use cell referencing in
What’s it about?
Look at this spreadsheet
We use labels to make the
spreadsheet easier to understand.
There’s no way to tell. The data is all there, but we can’t interpret it unless we know what the data concerns.
Data labels
Data labels can be formatted to make them clearer.
Numeric data can also be formatted to make it clearer.
The font, alignment, border and patterns can be changed.
The type of number can be
changed to currency, date, time,
percentage and fraction format. In this case, the prices have
been changed to currency with 2 decimal places.
What we have so far is a list. Quite a useful list, perhaps, but it doesn’t do very much. If we turn it into a model, we can work out bills for stationery. We need to:
enter the quantity of stationery the person bought
work out the cost of each item by multiplying price by quantity
add up the total bill at the end.
We needed to add some extra columns, rows and data labels.
Adding the formulas
Formulas can be used to save a lot of time.
For example, if we want to work out the item cost for folders, we can enter the formula =B3*C3 into cell D3. Now whatever value we add to cell C3 it will automatically multiply it by the folder price and give the total cost.
When copying and pasting a cell, it is the formula that is copied, not the answer. The formula in cell D3 was copied into D4.
Look what happened – it changed. Why?
It’s because we dropped down one row, so the formula
adjusted to the new row number. That’s called replicating
a formula.
When the formula was copied into D4 the computer
remembered “the cell that is 2 cells to the left multiplied by the cell that is 1 cell to the left”.
That is why it copied it as =B4*C4 when we moved down a row.
It related the reference, which is really useful when we replicate formulae.
This is called relative cell referencing.
We can work out the total bill as shown.
In a big spreadsheet it would take ages to click all the cells to add.
If we use the
sum function, we type =SUM(…
Then we highlight the cells we want added up, type )
and enter.
The formula becomes =SUM(D3:D7).
Once all the formulas have been entered it is time to add the numeric data.
As you input the numbers you will notice that the spreadsheet works out the formulas that you have placed and the answers are immediate.
Speed of calculations is one of the advantages of using a spreadsheet.
We can enter quantities and work out bills.
We can also experiment to answer “What If” questions.
What if Peter has £10 to spend – what else can he buy? He could buy an extra pen set and an extra pencil, but that isn’t the only answer. What other combinations could he choose?
Functions are “formulas already prepared”.
Some spreadsheet software have over 250 functions ready to use.
An example is the SUM function.
The sum function will add any cells you want together – much quicker when you have lots of cells to add.
Functions usually need a function name and a range.
Instead of typing = B7+B8+B9, we could type
=SUM(B7:B9)
Function (add up) Range (all the cells
between B7 and B9)
When you want a cell
reference to stay absolutely
the same you need to place
the $ sign in between the
letter and number.
Now when it is copied and pasted the cell B2 remains absolutely the same, but B3 changes as before.
This is called absolute cell referencing.
Identify the purpose of the model.
Plan the layout – it must be planned in a way that is
simple to use.
Draw a plan on paper to include the inputs,
processing and outputs that are required of the model.
Start creating the spreadsheet by entering all the data
labels, followed by the formulas and then the numeric data.
There are two types of cell referencing in formulas:
relative and absolute.
Functions are formulas that have already been
programmed.
All cells can be formatted.
