Lesson 6: Tables of values and spreadsheets

A spreadsheet of heater running costs

SkillsTables of valuesSpreadsheet formulas

Worked example

  • Complex familiar
  • Technology active

Jarrah sets up a spreadsheet to find the cost of running a 2.4 kW heater. Cell B1 holds the electricity price, 0.32 ($ per kWh), and B2 holds the heater's power, 2.4 (kW). The running times 1, 2, 3, 4, 5 and 6 hours are in A5 to A10.

(a) Write a formula for B5 that calculates the cost and can be filled down to B10.

(b) Complete the table of costs, to the nearest cent.

(c) What is the longest whole number of hours the heater can run for less than $4?

1 of 4 steps
  1. Step 1: Build the formula

    Cost = power × time × price =Pt×price= Pt \times \text{price}. The power and the price are fixed, so lock them: =$B$2*A5*$B$1

Try a similar one

A fresh question of the same kind, checked as you answer, with hints and a full solution.

Method

  1. Translate the formula into spreadsheet syntax with cell references.

  2. Lock any constants with absolute references before filling down.

  3. Check one row by hand, then read the table to answer the question.

Watch out for

  • Writing =B2*A5*B1: filled down, B1 and B2 drift to B2, B3, … and the costs are wrong.

Save your progress with a free account