Bitesize has changed! We're updating subjects as fast as we can. Visit our new site to find Bitesize guides and clips - and tell us what you think!
Print

ICT

Modelling with spreadsheets

Page:

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  1. Next

Computer models of mathematical data [data: information 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 or formulaeformula: A 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
3ProduceUnitNumber soldPriceSales
4Appleskg7£0.70£4.90
5Potatoes25kg8£6.00£48.00
6Orangeskg6£0.90£5.40
7Carrots25kg8£8.50£68.00
8Sproutskg4£1.40£5.60
9Cabbagekg6£0.70£4.20
10Onionskg9£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+E10 which will give the same result but is overly complex and leaves more room for mistakes. Note, the =SUM function should only be used when adding a range of cells.

Page:

  1. 1
  2. 2
  3. 3
  4. 4
  5. 5
  1. Next

Back to Modelling and simulation index

BBC © 2014 The BBC is not responsible for the content of external sites. Read more.

This page is best viewed in an up-to-date web browser with style sheets (CSS) enabled. While you will be able to view the content of this page in your current browser, you will not be able to get the full visual experience. Please consider upgrading your browser software or enabling style sheets (CSS) if you are able to do so.