1.3: Spreadsheet tutorials
- Page ID
- 435317
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)What do I hand in?
This lab is asynchronous. This means you can work on it at your own pace. Each lab section has one cancelled lab session (either the week of Indigenous People Day or the week of Thanksgiving, depending on which week day you meet. The lab is due that week, but it makes more sense to work through the topics earlier, as the skills you learn are useful to have in the lab.
To receive credit for this lab, please hand in printouts of one data table with descriptive statistics (part 1), one data table where at least one of the columns is calculated with a formula you entered yourself (part 2), and one scatterplot where you fitted data to a line. Ideally, these would be from data you collected yourself, for the first and second lab, as recorded in your lab notebook. If that is not possible, contact your instructor for an alternative.
Part 1: Data tables and descriptive statistics
Here are the slides used in the video.
Data tables
Here is an example of a data table for measuring the mass of some samples:
| Object | m1 (g) | m2 (g) | m3 (g) | average mass (g) |
| Apple | 154.627 | |||
| Banana | ||||
| Carrot |
You could make the data table headers before you start the lab, and then add the data as you measure it.
Descriptive statistics
When you measure something multiple times, there will be some variation in your measurements. You can summarize the measurements by giving the average, the standard deviation, and N, the number of measurements. These values give you an expectation of what to expect of additional measurements. In our example above, we are just calculating the average, i.e. the sum of the measurements divided by N, the number of measurements. You could do this using a pocket calculator but that becomes tedious (and error-prone) when having to calculate a lot of averages. Instead, with an electronic spreadsheet, you can enter a formula into the column for the average, and the spreadsheet will do the math. As an added bonus, it is very easy to copy and paste the formula into the remainder of the column. The video tutorial will show you how to enter a formula, and how to copy and paste it.
Apart from average and standard deviation, you might also be asked to calculate median, standard error and relative error. Here is a brief summary of why these are useful.
Average: Also called "mean". If your experiments does not have any systematic errors, the average will be closer to the true value than individual measurements because random errors cancel out when averaging.
Median: You expect 50% of your measurements to be higher than the median, and 50% to be lower.
Standard deviation: Describes the spread of the measurements around the average. If the deviation is due to small random errors, about 2/3 of the measurements will be within one standard deviation from the average.
Standard error: an estimate how far from the true value your average is. The idea is that the average is a better estimate than any single measurement because you are averaging out random errors. To get the standard error, you divide the standard deviation by the square root of the number of measurements. The more measurements, the smaller the standard error. For example, comparing the average of 10 measurements to 40 measurements (i.e. four times as many), the standard error gets cut into half. A quick way of giving average and standard error is to write something like "m = 45.33 ± 0.04 g".
Relative error: This is giving the standard deviation or standard error as a percentage of the average. You could say that the relative volume error of a pipette is 0.5%. For a volume of 100 mL, you would expect to be off by about 0.5 mL. Or you could say the average of your measurements is expected to be off by 2%, which would be the standard error divided by the average.
Part 2: Repetitive calculations
Sometimes, you have to do the same set of calculations for every measurement in your experiment. It makes sense to use a spreadsheet for these repetitive calculations because it is convenient and you can avoid mistakes. Even if the arithmetic is taken care of for you, you still are responsible for correct data entry, formulas, units and significant figures.
Part 3: Plotting data and fitting a line to it
This is a brief tutorial showing you how to plot your data (for a lab report or presentation). It also shows you how to fit a line to your data in the scatter plot. Data fitting within a spread sheet is more reproducible than with pencil, paper and ruler (and faster).
Here is the finished spread sheet. When you click on the link, it asks you whether you want to make a copy of it, and you should say yes if you want to edit a copy of it.