Session 12: Functions & Data Quality in Sheets
'F' → Fullscreen
Build
Step 1: Structure Your Table
Transfer your Session 11 data into Sheets: clearly labeled headers with units included (e.g., Weight (g), Trial 1 (mm)), one row per weight.
Step 2: Add an Average Column
For each weight's row, use =AVERAGE(range) to compute the average of its 3 trials. Reference the actual trial cells — don't type in a number you calculated by hand.
Step 3: Add Min and Max Columns
For each row, add =MIN(range) and =MAX(range) for the same 3 trial cells.
| Weight | Trial 1 | Trial 2 | Trial 3 | Average | Min | Max |
|---|---|---|---|---|---|---|
Step 4: Sanity Check
For every row, confirm your Average falls between your Min and Max. If it doesn't, one of your three formulas is referencing the wrong cells.
Step 5: Add a Spread Column
Add one more column calculating Max − Min for each row. If Max is in F2 and Min is in E2, that is =F2-E2.
Step 6: Choose a Threshold and Flag It
Decide, with your instructor, what counts as "too much" spread for this experiment. Write down the number and one sentence for why. Then select your Spread column and set a conditional formatting rule that highlights anything greater than it.
Step 7: Investigate Any Flagged Row
For any row your rule highlights, write one sentence: measurement noise, or something real about that weight? If nothing gets flagged, write one sentence on whether that surprises you.
Stretch (Optional): Flag the Actual Trial
Instead of flagging the whole row, try a custom formula conditional formatting rule that highlights just the one trial cell furthest from that row's average — ask your instructor if you want to attempt this.
Checkpoint
Before you move on, confirm all of the following:
- Average, Min, and Max are actual formulas — not typed-in numbers
- Average falls between Min and Max for every single row
- Headers still include units, from your Session 11 structure
- Your Spread column uses a formula, not typed-in numbers
- Your conditional formatting rule is applied and visibly working
- Any flagged row has a written note explaining your read on it
Reflection
Answer these on your own. Reflections are individual, even when the rest of the session was team work.
- Why might a formula-based average be more trustworthy than one you calculated by hand and typed in?
- For any row that got flagged, do you think it is measurement noise or something real about that weight? How would you actually find out?
- Next session turns this data into a plot. Do you expect a flagged row to stand out as an outlier on the scatter plot too?
Submit
Submit your Sheets table with working Average, Min, Max, and Spread formulas, your conditional formatting rule, and your reflection.