Calculated columns in tables

Use it for practicals with repeated trials. Students enter their measurements row by row, and the table works out the derived quantity for each trial and summarises the column. One column formula applies to every row, so three trials or ten need the same setup.

The calculated cells are filled in by the table, not by students, so students can concentrate on measuring and on what the numbers show.

1. In a worksheet, use the + between two items and choose Fill the table. Write the column headers, for example Trial, Time and Speed, and give each number column the Data type Numerical and a Unit. Fill in and lock cells students should not change, such as the trial numbers.

2. Open Column options on the measurement column (Time) and choose Publish column as variables. Check the Display name and click Save.

3. Open Column options on the result column (Speed) and choose Use column formula.

4. Write the formula. To divide a distance of 24 m by each trial's time, type 24, type / and choose Time from Variables. Click Add.

5. To summarise the results, open Column options on Speed again and choose Mean or Sum under Column summary.

6. Click the tick when you are done.

Column options appears only on columns whose data type is Numerical. The same menu lets you change the formula later (Edit column formula) or remove it.

Students type their measurements into the input cells. Each calculated cell shows its result as soon as its row has the values it needs, and the summary updates with every entry. The mean counts only the rows that have a result, so it is incomplete until every trial is filled in. If a student corrects a value, that row and the summary change at once. They submit the table when they are done.

Open Results on the table to see each student's submitted table, with their measurements, the calculated values and the summary together. Calculations are redone from the saved measurements, so you see what the student saw. When you export results to Excel, calculated values are included as numbers.

• In the formula, choose the column itself (Time), not a numbered entry such as Time row 1. The column stands for the value in the same row; a numbered entry would repeat that one trial in every row.

• Type values that are the same for every trial, such as the distance, straight into the formula, and publish only the columns that change from row to row.

• Units are labels and nothing is converted, so ask for measurements in units that suit the formula.

• The mean is the mean of the row results, which is not always the same as one calculation from the totals. Say in the instruction which one you mean.