Lesson 6: Tables of values and spreadsheets
A spreadsheet of heater running costs
SkillsTables of valuesSpreadsheet formulas
Worked example
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?
Step 1: Build the formula
Cost = power × time × price . The power and the price are fixed, so lock them:
=$B$2*A5*$B$1A5 must change to A6, A7, … as the formula fills down, but B1 and B2 must not.
Step 2: Check the first row by hand
Step 3: Fill down
Hours 1 2 3 4 5 6 Cost ($) 0.77 1.54 2.30 3.07 3.84 4.61 Step 4: Read the table
5 hours costs $3.84 and 6 hours costs $4.61, so 5 hours is the longest whole number of hours under $4.
Answer
(a) =$B$2*A5*$B$1 (b) $0.77, $1.54, $2.30, $3.07, $3.84, $4.61 (c) 5 hours.
Try a similar one
A fresh question of the same kind, checked as you answer, with hints and a full solution.
Method
Translate the formula into spreadsheet syntax with cell references.
Lock any constants with absolute references before filling down.
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.