The Vault · Skills

Google Spreadsheets

Short clips showing how to handle your practical data in Google Sheets

Average your repeat trials

Find the mean of three trials in one cell, then fill it down for every row.

Click the clip to watch it full screen.

  1. Click the first empty cell beside your trials.
  2. Type =average(
  3. Drag across all three trials, then press Enter.
  4. Accept the auto-fill to average every row.
  5. Check: each mean sits between its three trials.

Round your means to match your data

Give every number in a column the same number of decimal places as your measurements: 2 or 3 decimal places.

Click the clip to watch it full screen.

  1. Select the whole column of means.
  2. Use the decimal buttons in the toolbar, .0← and .00→, to set 2 decimal places.
  3. Check: every number in the column has the same number of decimal places, including a final zero such as 6.60.

Divide a value

Turn the time for 5 periods into the time for one period by dividing by 5.

Click the clip to watch it full screen.

  1. Click the empty cell and type =
  2. Click the value you want to divide.
  3. Type / then the number to divide by, so the cell reads =E4/5
  4. Press Enter, then accept the auto-fill.
  5. Set 2 decimal places to match your data.
  6. Check: multiply one answer back, 1.41 × 5 gives 7.05.

Make a scatter graph

Plot your results against your independent variable, with the independent variable along the x-axis.

Click the clip to watch it full screen.

  1. Drag down your independent variable, header included.
  2. Hold Command on a Mac (Ctrl on Windows) and drag your results column.
  3. Click Insert, then Chart.
  4. Open the Chart type menu, scroll down and choose Scatter chart.
  5. Check: one point per row, with your independent variable along the x-axis.

Spread out the x-axis

Widen the x-axis so your points sit in the middle of the graph, clear of the left and right edges.

Click the clip to watch it full screen.

  1. Open Customise, then Horizontal axis.
  2. Set the Min. to 0.
  3. Set the Max. just beyond your largest value: for masses up to 1000 g, use 1200.
  4. Check: no point sits on the left or right edge.

Spread out the y-axis

Raise the top of the y-axis so your points sit comfortably inside the graph, clear of the top edge.

Click the clip to watch it full screen.

  1. Open Customise, then Vertical axis.
  2. Set the Min. to 0.
  3. Set the Max. above your largest value: for periods up to 1.41 s, use 2.
  4. Check: your points sit clear of the top.

Give your graph a title

Replace the automatic title with one of your own that names both variables.

Click the clip to watch it full screen.

  1. Open Customise, then Chart and axis titles.
  2. Click the title and type your own.
  3. Check: the title names both variables.

Add a trendline and its equation

Draw the line of best fit through your points and show its equation on the graph.

Click the clip to watch it full screen.

  1. Click one of your data points. The Series options open.
  2. Scroll down and tick the Trend line box.
  3. For a straight-line relationship, keep Type as Linear.
  4. Under Label, choose Use equation.
  5. Open Legend and set the font size to 14 so the equation is easy to read.
  6. Check: the line and its equation appear on your graph.

Analyse your trendline equation

Google Sheets writes the equation of a straight line in its own shorthand. Match it to y = mx + c to find the gradient and the y-intercept.

Step 1: Google Sheets shows -2.03E-04*x + 1.48. The number before x, -2.03E-04, is the gradient m. The number on its own, 1.48, is the y-intercept c. Step 2: E-04 means times ten to the minus four, so -2.03E-04 equals -2.03 times ten to the minus four, which is -0.000203. E-04 moves the decimal point 4 places left; E+03 moves it 3 places right. Step 3: multiply the gradient by the range of x to see how much y changes: -0.000203 seconds per gram times 800 grams is -0.16 seconds. Across the whole range of masses the period fell by only 0.16 seconds, small next to a period of about 1.4 seconds, so changing the mass of the bob has little effect on the period of the pendulum.
  1. Find m: the number in front of *x.
  2. Find c: the number on its own at the end.
  3. Rewrite any E number as × 10 to that power, then as an ordinary decimal.
  4. Give m its units: the y-axis unit over the x-axis unit, here s/g.
  5. Multiply m by your range of x to find how much y changes: here −0.000203 s/g × 800 g = −0.16 s.
  6. Compare that change with your values: 0.16 s is small next to a period of about 1.4 s.
  7. Check: the change across your range matches how much your line rises or falls on the graph.

Causation: does the independent variable cause a change?

In a fair test you change one variable and keep the others the same. So if the dependent variable changes, the independent variable caused it. Multiply the gradient by your range to see how big that effect is.

Step 1: two graphs. A large change across the range means changing the independent variable causes a large change in the dependent variable. A small change across the range, like the pendulum, means it has little effect. Step 2: we changed only the mass, keeping length and release angle the same; the period fell by only 0.16 s, from -0.000203 s/g times 800 g; so changing the mass has little effect on the period. Step 3: conclusion sentence: Changing the mass from 200 g to 1000 g had little effect on the period of the pendulum: across the whole range the period fell by only 0.16 s, small next to a period of about 1.4 s.
  1. Multiply the gradient m by your range of the independent variable to find the change in the dependent variable.
  2. Large change next to your values: changing the independent variable causes a large change in the dependent variable.
  3. Small change next to your values: changing the independent variable has little effect on the dependent variable.
  4. Write your conclusion: name both variables, the range you tested, and the size of the change as your evidence.

Correlation: how well do your points fit the line?

The trendline is your best-fit model. Correlation describes how closely your real data points follow it.

Step 1: three small graphs. Perfect correlation: every point sits exactly on the line. Strong correlation: every point sits close to the line. Weak correlation: the points are spread well away from the line. Step 2: the pendulum graph of period against mass, with the trendline; the points sit close to the line but not exactly on it, so this is strong correlation, not perfect. Step 3: sentence: Our data show a strong correlation with the trendline: every point lies close to the line, but not exactly on it.
  1. Look at the gap between each point and the trendline.
  2. Every point exactly on the line: perfect correlation.
  3. Every point close to the line: strong correlation.
  4. Points spread well away from the line: weak correlation.
  5. Write it: name the strength, then give the reason you can see on your graph.