Exploring Data in a Spreadsheet

A table with many students and several subjects contains useful information, but repeated addition can become slow. A spreadsheet keeps data in a grid and calculates from that grid. If an entry changes, a formula can recalculate its result automatically.

The software follows the instructions you give it. It cannot decide whether you selected the correct students, included the right columns, or typed a sensible value. Learning to read a formula is therefore as important as learning to enter one. We will start with a small activity-score table that you can reproduce easily.

Spreadsheets

Columns are labelled with letters, and rows are labelled with numbers. A cell address combines the column and row: B3 means column B, row 3. A rectangular collection of cells is called a range. The range B2:D2 includes B2, C2, and D2; B2:B5 includes four cells down one column.

Enter the headers in row 1 and the data in rows 2–5. The row labels shown at the left below are worksheet row numbers, not an extra data column. Column A holds names, B through D hold three activity scores, E will hold each total, and F will hold each mean.

Using SUM and AVERAGE

SUM adds the numbers in a range. AVERAGE calculates their mean. For Asha’s row, =SUM(B2:D2) totals three scores, while =AVERAGE(B2:D2) divides that total by the number of numeric entries in the range.

These formulas are not just button presses. Say them aloud: “Add the cells from B2 through D2,” or “Find the mean of the numeric entries from B2 through D2.” This habit makes accidental range choices easier to notice.

Copying Formulas Down and Across

A normal cell reference is relative. When a formula is copied one row down, its row references move one row down too. Copying =SUM(B2:D2) from E2 to E3 therefore produces =SUM(B3:D3). This is helpful because each student needs a total from their own row.

Copying across changes columns. Suppose B7 contains =AVERAGE(B2:B5). Copying it one column right to C7 changes the formula to =AVERAGE(C2:C5). Always inspect the resulting formula to confirm that the movement matches your intention.

Comparing Columns

A row calculation answers a question about one student across activities. A column calculation answers a question about one activity across students. Both are useful, but they describe different groups of observations.

For example, a high Reading-column mean describes these four Reading scores together. It does not say that every learner did better in Reading than in Puzzle. To make that claim, compare the relevant scores within each learner’s row.

Checking the Range and the Data

Do not include a total alongside the individual scores when calculating their mean. In Asha’s row, B2:E2 includes 12, 15, 18, and 45. Averaging those four numbers answers the wrong question because the total is counted as if it were an extra activity score.

A blank cell and a recorded zero are different. A blank may mean that an observation is missing; zero is an actual numeric value. Common spreadsheet AVERAGE functions ignore empty cells but include numeric zeros. Check that behaviour in your sheet and record missing data honestly rather than silently replacing it with zero.

Build a Small Data Sheet

Create the displayed table in a spreadsheet and use it as a checking exercise.

1. Enter the labels and data, then calculate one row manually.
2. Add SUM and AVERAGE formulas and copy them to the other rows.
3. Calculate the three column means.
4. Change Asha’s Puzzle score from 12 to 15 and predict the affected results before checking the sheet.

Asha’s new total is 48 and her new row mean is 16. The Puzzle-column mean rises from 13 to 13.75. The other two column means stay the same. This is a useful check that the formulas refer to the intended cells.