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

Constructing a spreadsheet

A self-employed builder charges £15.00 an hour. He's constructed a spreadsheet [spreadsheet: A 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 the builder's incomplete spreadsheet.

Download the incomplete spreadsheet on the left and perform the tasks below. To open it you will need a spreadsheet application, eg Microsoft's Excel, OpenOffice.org's Calc, Google Docs or another application that can open *.xls files.

Please note that the actual steps required to complete each tasks may differ slightly depending on the spreadsheet application you're using.

Task 1

The builder has just completed some work for Jason Williams. It took him four hours and the materials cost £37.00.

  1. Enter Jason's information into the spreadsheet.

The formulae to calculate the bill should be automatically inherited from the cell above.

Task 2

Changing the category to currency

Changing the category to currency

The values stored in columns C and D are currency. Change the number category to currency so the spreadsheet recognises this.

  1. Click cell C2 and hold down the left mouse button. Move the cursor across to cell D2 and then down to cell D6. The cells should now be highlighted.
  2. Release the left mouse button and right-click on a highlighted cell. Click Format Cells.
  3. A new screen appears, click the Number tab and click currency in the list.
  4. Click OK.

Task 3

Modifying a spreadsheet formula

There's a mistake in the formula used to calculate the total bill, the builder has entered his hourly rate incorrectly - correct his mistake (remember, his hourly rate is £15).

  1. Double-click cell D2 to edit the formula. Make the correction and press enter on the keyboard to save the changes.
  2. Copy the changes to the formula to the cells below. Click cell D2, left-click the small black square in the bottom right corner and drag it down to cell D6. Release the left mouse button.

Task 4

A sum formula being applied to several cells

You can select specific cells to use in your formula

The builder wants to calculate his daily earnings, write a formula in cell D8 to do this for him. Press enter to save the changes.

The formula can be written two ways, decide which one to use:

  1. =D2+D3+D4+D5+D6
  2. =SUM(D2:D6)

The spreadsheet program should realise you're adding together currency and automatically change cell D8's number category to match.

Document

Download the builder's completed spreadsheet.

Download the completed spreadsheet on the left to see how you did or to learn where you went wrong.

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.