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 *, /, ^ and SQRT().
  • 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:

T=40m+20T = 40m + 20

where TT is the cooking time in minutes and mm 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.

Roasting times from T=40m+20T = 40m + 20. Each output is one substitution: for m=4.5m = 4.5, T=40(4.5)+20=200T = 40(4.5) + 20 = 200.
Mass, mm (kg)33.544.55
Time, TT (min)140160180200220

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: 40m+2040m + 20 becomes =40*A2+20, and mh2\dfrac{m}{h^2} becomes =A2/B2^2.
  • Use SQRT() for a square root: 3.57h3.57\sqrt{h} becomes =3.57*SQRT(A2).
  • Spreadsheets follow the usual order of operations, so add brackets where the formula has a fraction line.

Formulas view

AB
1Mass (kg)Time (min)
23=40*A2+20
33.5=40*A3+20
44=40*A4+20

Values view

AB
1Mass (kg)Time (min)
23140
33.5160
44180

In the turkey spreadsheet, 6.5 is typed in cell A7 and the formula is filled down to B7. What value does B7 show?

in min

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

  • 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?

4 steps

Try it yourself first, then check the working one step at a time.

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.

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, D=yy+12×AD = \dfrac{y}{y + 12} \times A, and still works when filled down?

Choose one answer

A table of values is also a decision tool. Lay two formulas side by side and you can see where one overtakes the other.

Interactive

When does the new fridge pay for itself?

The horizontal axis is time yy 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?

  • Complex unfamiliar
  • Technology active

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 yy years is Cold=192.72yC_{\text{old}} = 192.72y, and that the total cost of buying and running the new fridge is Cnew=1450+108.405yC_{\text{new}} = 1450 + 108.405y.

(b) Construct a table of values of both costs for y=0,5,10,15y = 0, 5, 10, 15 and 2020.

(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.

5 steps

Try it yourself first, then check the working one step at a time.

Method

  1. Build each total-cost formula as a fixed amount plus a rate per year.

  2. Tabulate both to see roughly where they cross.

  3. Set the formulas equal and solve for the crossover.

  4. 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, hh metres, are listed from A2 down. Which formula in B2 calculates the distance to the horizon, d=3.57hd = 3.57\sqrt{h}?

Choose one answer

Common mistake

Spreadsheet slips

  • Leaving out =, so the spreadsheet shows your formula as text.
  • Leaving out *: =40A2+20 is an error.
  • Forgetting to lock a constant: a plain B1 drifts 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 *, /, ^ and SQRT().

  • 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.

Lesson 6 of 70% read

Save your progress with a free account