Guided project
Lab experiment analysis in Excel
Average trials, measure percent error, and flag readings to recheck.
You will use: AVERAGE, ABS, IF, AVERAGEIF, STDEV.S
Starting data
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Trial | Group | Reading 1 | Reading 2 | Mean | % error | Check | Accepted g | 9.81 | |
| 2 | T1 | A | 9.78 | 9.86 | Group A mean | |||||
| 3 | T2 | A | 9.91 | 9.69 | Spread (SD) | |||||
| 4 | T3 | B | 10.42 | 10.38 | ||||||
| 5 | T4 | B | 9.62 | 9.7 | ||||||
| 6 | T5 | A | 9.8 | 9.83 | ||||||
| 7 | T6 | B | 9.4 | 9.55 |
Build it step by step
Step 1
Average the two readings for each trial in E2, fill to E7.
Step 2
Percent error is the distance from the accepted value in J1, divided by J1. Write it in F2, fill to F7.
Step 3
Flag any trial over 3% error as "Recheck", otherwise "OK". Start in G2, fill to G7.
Step 4
What is the mean for Group A only? Put it in J2.
Step 5
How spread out are the means? Find the sample standard deviation in J3.