Lesson 6 of 7 · 18 min
Tables of values and spreadsheets
Build a table of values from a formula by hand and in a spreadsheet, write spreadsheet formulas with relative and absolute references, and use the table to compare options.
What you will be able to do
- Construct a table of values from a formula.
- Write a formula in spreadsheet syntax, using
*,/,^andSQRT(). - Fill a formula down a column, using an absolute reference (such as $B$1) for a value that must stay fixed.
- Use a table of values to compare options and find when one becomes better than another.
A butcher's guide says to roast a turkey for 40 minutes per kilogram, plus an extra 20 minutes:
where is the cooking time in minutes and is the mass in kilograms. Customers buy turkeys of all sizes, so instead of one substitution the butcher wants a table of values: the same formula evaluated for a list of inputs.
| Mass, (kg) | 3 | 3.5 | 4 | 4.5 | 5 |
|---|---|---|---|---|---|
| Time, (min) | 140 | 160 | 180 | 200 | 220 |
Doing ten or a hundred substitutions by hand is slow and error-prone. A spreadsheet does them for you. You type the inputs in one column, write the formula once using a cell reference in place of the pronumeral, and fill it down. The spreadsheet adjusts the reference for each row.
Key idea
Writing a spreadsheet formula
- Start with
=. - Use a cell reference, such as
A2, wherever the formula has a pronumeral. - Use
*to multiply,/to divide and^for a power: becomes=40*A2+20, and becomes=A2/B2^2. - Use
SQRT()for a square root: becomes=3.57*SQRT(A2). - Spreadsheets follow the usual order of operations, so add brackets where the formula has a fraction line.
Formulas view
| A | B | |
|---|---|---|
| 1 | Mass (kg) | Time (min) |
| 2 | 3 | =40*A2+20 |
| 3 | 3.5 | =40*A3+20 |
| 4 | 4 | =40*A4+20 |
Values view
| A | B | |
|---|---|---|
| 1 | Mass (kg) | Time (min) |
| 2 | 3 | 140 |
| 3 | 3.5 | 160 |
| 4 | 4 | 180 |
In the turkey spreadsheet, 6.5 is typed in cell A7 and the formula is filled down to B7. What value does B7 show?
Sometimes a formula uses a value that is the same in every row, such as the price of electricity. Put it in its own cell and refer to it with an absolute reference: $B$1. The $ signs lock the column and the row, so the reference does not change when you fill down. Then changing the price in B1 updates the whole table at once.
Worked example
A spreadsheet of heater running costs
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.
Try it yourself first, then check the working one step at a time.
Answer
(a) =$B$2*A5*$B$1 (b) $0.77, $1.54, $2.30, $3.07, $3.84, $4.61 (c) 5 hours.
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.
Cell B1 holds the adult dose of a medicine, in mg, and ages in years are listed from A4 down. Which formula in B4 calculates Young's rule, , and still works when filled down?
A table of values is also a decision tool. Lay two formulas side by side and you can see where one overtakes the other.
The horizontal axis is time in years and the vertical axis is total cost in dollars. The new fridge (accent) costs $1450 up front but less to run; the old fridge (red) costs nothing up front but more each year. Trace to where the lines cross.
Worked example
Is a new energy-efficient fridge worth it?
The Nguyen family's old fridge uses 1.6 kWh of electricity a day. A new energy-efficient fridge costs $1450 and uses 0.9 kWh a day. Electricity costs 33 cents per kWh.
(a) Show that the total cost, in dollars, of running the old fridge for years is , and that the total cost of buying and running the new fridge is .
(b) Construct a table of values of both costs for and .
(c) Find when the new fridge becomes the cheaper option.
(d) Fridges typically last 10 to 15 years. Evaluate whether buying the new fridge is worthwhile on cost alone.
Step 1: (a) Build the old fridge's formula
Cost per year , so .
Step 2: (a) Build the new fridge's formula
Running cost per year . Add the purchase price: .
Step 3: (b) Table of values
(years) 0 5 10 15 20 ($) 0 963.60 1927.20 2890.80 3854.40 ($) 1450.00 1992.03 2534.05 3076.08 3618.10 The old fridge is cheaper at 15 years but dearer at 20 years, so the costs are equal somewhere between.
Step 4: (c) Set the costs equal and solve
Subtracting from both sides collects the unknown on one side. is the saving per year.
Step 5: (d) Evaluate
The new fridge only pays for itself after about 17.2 years, longer than a fridge usually lasts. On cost alone it is not worthwhile. It may still make sense if the old fridge is about to fail, if electricity prices rise, or for environmental reasons.
Try it yourself first, then check the working one step at a time.
Answer
(c) After about 17.2 years. (d) On cost alone, no: the saving of about $84 a year takes longer to repay $1450 than the fridge's likely life, unless the old fridge needs replacing anyway or electricity prices rise.
Method
Build each total-cost formula as a fixed amount plus a rate per year.
Tabulate both to see roughly where they cross.
Set the formulas equal and solve for the crossover.
Judge the result against the real-world constraints.
Watch out for
Forgetting to convert 33 cents to $0.33.
Concluding from the table alone that the crossover is at 15 or 20 years. Solve the equation for the exact point.
Heights above sea level, metres, are listed from A2 down. Which formula in B2 calculates the distance to the horizon, ?
Common mistake
Spreadsheet slips
- Leaving out
=, so the spreadsheet shows your formula as text. - Leaving out
*:=40A2+20is an error. - Forgetting to lock a constant: a plain
B1drifts to B2, B3, … as you fill down. - Missing brackets around a fraction's numerator or denominator.
Summary
A table of values evaluates one formula for a list of inputs.
Spreadsheet formulas start with
=and use*,/,^andSQRT().Relative references such as A2 change as you fill down; absolute references such as $B$1 stay fixed.
Put constants such as prices in their own cell, so one change updates the whole table.
Tables of two formulas side by side show when one option becomes cheaper than the other.
Finished the lesson?
Try the 3 quick checks above and the lesson completes itself, or mark it complete now.