modelling with spreadsheets

HideShow resource information

Computer models of mathematicaldata [datainformation without context, eg a list of students with numbers beside their names is data, when it's made clear that those numbers represent their placing in a 100 metre race, the data becomes information ], such as budgets, are usually done using a spreadsheet application that processes and performs calculations on the data entered by the user. For example, a computer model of a school's budget might show that the school will run out of money before the end of the term if it hires an extra teacher.

How spreadsheets work

A spreadsheet appears as a grid, each row has its own number and each column its own letter. This labelling of rows and columns is used to give each cell a cell address or reference, for example, C5 means column C, row 5.

It is also possible to refer to a range of cells collectively, ie E4:E12 includes E4, E12 and all of the cells in-between.

Cells can contain numbers, text orformulaeformulaA formula is a combination of symbols that indicates the chemical composition of a substance..

Example spreadsheet

A grocer uses a mobile shop to sell groceries to customers who live out of town. He uses a spreadsheet to keep a record of his daily sales:

Daily sales

 ABCDE 3 Produce Unit Number sold Price Sales 4 Apples kg 7 £0.70 £4.90 5 Potatoes 25kg 8 £6.00 £48.00 6 Oranges kg 6 £0.90 £5.40 7 Carrots 25kg 8 £8.50 £68.00 8 Sprouts kg 4 £1.40 £5.60 9 Cabbage kg 6 £0.70 £4.20 10 Onions kg 9 £0.56 £5.04 11           12       Total £141.14

There are a number of formulae in the above spreadsheet but they're hidden, only the results are displayed.

Formulae

The formula in cells E4 to E10 works out the total value of sales for each item. It does this by multiplying the number sold by the individual price. For example, seven kilograms of apples were sold at 70p per kilogram, the sales total is calculated as £4.90 and is displayed in the cell E4. The formula in E4 is=C4*D4, ie 7 × £0.70.

Cell E12 contains a formula that calculates the total value of the sales column. The formulae used is=SUM(E4:E10). This formula includes cells E4, E10 and all of the cells in-between. You could write it=E4+E5+E6+E7+E8+E9+E10which will give the same result but is overly complex and leaves more room for mistakes. Note, the=SUMfunction should only be used when adding a range of cells.

Constructing a spreadsheet

A self-employed builder charges £15.00 an hour. He's constructed aspreadsheetspreadsheetA spreadsheet is made up of cells, rows and columns. Each cell holds a piece of numeric (numbers) or alphanumeric (text) data. Cells can also contain formulae to calculate their contents.to keep a record of his work but it's incomplete.

Complete the builder's spreadsheet

Document

Download…

Comments

No comments have yet been made

Similar ICT resources:

See all ICT resources »See all Spreadsheets and Databases resources »