Spreadsheet formulas – functions and copying cell references
Computer ScienceDigital ApplicationsAges 12–13
Use it with your class: students open it from a link or a QR code, with no account needed.
Next in Digital Applications
A small working spreadsheet: click a cell to select it, type a number or a formula starting with = into the formula bar, then press Enter to see the result instantly. Buttons insert the SUM, AVERAGE, MAX, and MIN functions, and other buttons copy a formula down or to the right so you can watch relative references update automatically while an absolute reference like $B$9 stays fixed.
Lesson: Automatic calculation in spreadsheets: formulas, the SUM/AVERAGE/MAX/MIN functions, relative and absolute references
What it shows
A spreadsheet stores values in cells named by column letter and row number, and a formula calculates a new value from other cells. This sheet evaluates formulas as you type them, supports SUM, AVERAGE, MAX and MIN over ranges such as B2:D2, and highlights every cell a formula refers to. The key idea is what happens when a formula is copied: a relative reference like B2 shifts by the same number of rows and columns as the copy, while an absolute reference with $ signs always points to the same cell. That is why a shared conversion factor uses an absolute reference.
How to use
Choose a Dataset, click cell E2 and read its formula in the formula bar. Press Copy formula down, then click the copied cells one by one to see how the row numbers changed. Next, select an empty result cell, press Formula with absolute $ and copy again to compare the two kinds of reference. Use Show formulas to see every formula at once, and ask students to predict each result before pressing Enter.
Parameters you can change
- Dataset Three-subject grade sheet, Four-quarter sales, Height measured three times
- Number of data rows 3–6 rows
- Suggested function in the result column SUM – total, AVERAGE – arithmetic mean, MAX – maximum value, MIN – minimum value
- Show formulas instead of results
Questions to explore
- If E2 contains =SUM(B2:D2), what formula appears in E3 after you copy it down one row?
- Why must the conversion factor be an absolute reference when the formula is copied down the column?
- What happens to the result cells when you change one number in the data, and why?
