This is the first week of a two-week lab studying cell motion. This week we will learn how to use
Excel to analyze the 1-D motion of an amoeba from stop-motion images. Next week we will be
analyzing videos of cell motion: 1) wound closure, 2) neutrophil motion, and 3) bacteria
motion—to determine whether or not a patient should be prescribed antibiotics. Clearly, the
relative speeds of the wound closure, neutrophils, and bacteria will affect your decision. Thus it
becomes important that we learn how to quantify the motion of cells.
On the next page, you will see a graph of the movement of Dictyostelium discoideum, shown as a
sequence of outlines of the amoeba cell at 3.0-minute intervals. Your task is to record and analyze
the motion of the amoeba—specifically, the position, instantaneous and average speed, and
instantaneous and average acceleration. Rather than do all of the mathematical calculations by
hand, Excel can help you do the calculations much more quickly and efficiently. This week you
will practice and master the skills necessary to bend Excel to your will and make it do the grunt
work. Next week, you will be expected to be experts at these skills so take turns and help each
other learn.
Deliverables:
Either individually or with a partner, you will submit a set of graphs (y vs. t, v vs. t) with your
data tables and the typed responses to these questions:
1) What do these graphs say about the motion of this organism?
2) Look at the top speed for the organism. If the organism were moving at the top speed for an
entire day (24 hours) how far would it travel. Compare this to the length of the human
body.
3) What did you learn from this first online lab? What do you think the instructors are hoping
you start to learn and practice from this?
You will upload the graphs, tables, and typed responses in one PDF to the assignment page in
canvas. It is incredibly hard to grade if there are several pages of uploads.
_________________________________________________________________________________________________________________Ad
apted from K. Moore, J. Giannini, B. Geller & W. Losert (Univ. of Maryland, College Park)
1
2
Table 1: Your position vs. time graph – Using the figure above, find the position for the amoeba
for each time given in the graph. Remember, each snapshot of the amoeba is taken in three second
intervals (hence the intervals given in the graph). For consistency it is best to pick the same part of
the amoeba every time (say the top point). When you are done create a graph of your position vs.
time. Usually we use a scatter plot for data like this but this time we will use a line graph. Be
careful to show units! You may use either google spreadsheet or excel. There is an excel review on
the next page.
t y
0
3
6
9
12
15
18
21
24
27
30
3
Table 2: Your velocity vs. time graph – Remember, your velocity is Δy/Δt , so if you can
calculate the average velocity ( y2
– y1
)/ (t2 − t2
) during each time interval in the table above. As an
approximation, we can say that this average velocity occurs in the middle of the time interval so
1.5s for the first velocity, 4.5s for the second velocity, etc, as can be seen in the table below. Once
you complete the table, make a graph for velocity vs. time.
t v
1.5
4.5
7.5
10.5
13.5
16.5
19.5
22.5
25.5
28.5
4
Excel Tutorial Reference
Intro to Scientific Data Analysis with Excel
Excel (or any spreadsheet program) can help you do calculations quickly and efficiently. But Excel
is only a software program—it can only be as smart as the instructions that you give to it. Today
you will practice and master the skills necessary to bend Excel to your will and make it do the
grunt work. After today, you will ALL be expected to be experts at these skills so take turns and
help each other learn. Take notes for the future if you are worried that you will forget. Each
version of Excel (as with the other Microsoft Office programs) varies slightly, but familiarity with
one version should help you intuitively guess/explore the other versions. Some of you may feel
that you are already familiar with Excel; please READ the Technical document anyway! It
contains specific scientific norms that you need to learn.
Entering Data
To enter information in a cell, click on the cell (e.g., cell A1—column A, row 1) and type the
information. When you are finished, press ‘Enter.’ You will see the information in the cell. To edit
the information, click on the cell and type (erases previous entry) or click on the cell and then click
on the formula bar to edit (the formula bar is above all the cells but below all the icons for text
manipulation (bold, italicize, center in cell, etc.).
Title each column with the quantity’s name and units. In the cells beneath each column title, enter
the data that you have collected by typing the numbers into the cells. When you have finished
entering your data, save the file by pressing ‘Ctrl’ + ‘C’ (name the file and note the saved location
for future use) or by selecting the ‘File’ tab, then the ‘Save as’ icon. Save regularly to avoid losing
work. Every time you save the file, it overwrites the previous version.
The number of decimal places displayed in the cell can be controlled by icons in the ‘Number’
menu on the ‘Home’ tab. Use these icons to give your data uniform appearance.
5
Generating Columns of Data Calculated from a Formula
To generate information from a formula (i.e., to mathematically manipulate cells), click on a cell
and begin with =. All Excel entries for which you expect a numerical result/output MUST begin
with an equals sign, =. The mathematical manipulators are what you would expect: * to multiply, /
to divide, + to add, and – to subtract. To exponentiate, type ^ and the power (e.g., 62 is 6^2). Let us
say you wished to multiply the entry in cell A1 by 60. To do this you would click on the cell where
you wish the output to go and then type ‘=60*A1.’ You can type the input cell, A1, on the
keyboard, or you can use the mouse to click on cell A1 while typing the formula. For more
complex mathematical operations, parentheses often become necessary. Excel will color-code the
parenthesis to help you see where each set opens and closes. Be VERY careful with your
parentheses! (Again, Excel is only as smart as the instructions that you give to it!)
If you wish the same mathematical formula to be applied to every cell in a column (e.g., column F
is column A times 60), type the formula into the output cell for the first row. Then click on the
output cell and move your mouse over the bottom, right corner of the cell. Your pointer will
change shape and become a plus. Press down the left mouse button, drag down to the last cell you
wish to effect, then release the mouse button. The cells will automatically fill in. By clicking on
any of these cells, you can see in the formula bar that the formula has been adjusted to reference
the correct input cell (i.e., for the row you are currently in). The same process can be used to copy
a formula across a row into multiple columns. If, in a formula, you wish to reference a specific
column or cell (that will NOT change when the formula is dragged over a column or row), use the
$ symbol: e.g., $A1 will always be column A, but the row number can change; and $A$1 will
always be column A and row 1—neither can change.
Generating Graphs
When creating a graph/plot, Excel will usually plot the first column on the independent/horizontal
axis and the second column on the dependent/vertical axis. You should always check that the
correct data has appeared on the correct axis. If the data has been entered with the columns in
reverse order, this can be fixed after the plot is created. To generate a graph/plot, use the mouse to
highlight the data that you wish to graph (if the data is adjacent, just highlight the entire chunk; if
the columns are not adjacent, highlight each column separately while holding the ‘Ctrl’ key). Once
the data is highlighted, click on the ‘Insert’ tab and then the ‘Scatter’ icon (on the ‘Charts’
menu—other types of charts may be appropriate for different data types, but the Scatter plot is the
most commonly used).
6
From the sub-types of scatter plot available, choose the one that hasdata points but no lines. A
graph will appear—but you are NOT finished yet! The design of the graph can be adjusted using
the ‘Chart Layout’ menu—you will want to be able to label the individual axes with the quantity
graphed and its units and you will want to be able to title the graph. When the graph has been titled
(for the ‘vs.’ format, it is always ‘Dependent’ vs. ‘Independent’) and the axes have been properly
labeled, you can choose to keep or delete the Legend (not needed if only one data set is plotted,
absolutely necessary for multiple data sets). The location of the chart can be changed by:
left-clicking on the chart (the upper right corner works well), then right clicking and choosing
‘Move Chart’ from the menu. It is often best to make the chart a ‘New Sheet’ (so that it doesn’t
block your view of the data)—give it an appropriate title and click ‘Okay.’ You chart will become
its own sheet—and your data will disappear! But the data is not gone—to find it, use the tabs on
the bottom of the Excel window (‘Sheet 1’ is usually the data; now might be a good time to rename
it ‘Data’: right click, select ‘Rename’ and name it).
Before you finish, check that the correct data has been plotted on the correct axis and that the axis
labels match the quantities plotted. If the original columns were in reverse order, this can be
adjusted by left-clicking on a data point (to select the data), and then right-clicking and choosing
‘Select Data’. Click on the Legend Entry you wish to adjust, and then click ‘Edit.’ Highlight the
appropriate Series X and Series Y data and then click ‘Okay.’
Adding a Best Fit (“Trendline”) to a Graph:
To add a line of fit (called a “Trendline” in Excel) to a graph, left-click on a data point to select the
data, then right-click and choose ‘Add Trendline.’ Choose the appropriate Trend/Regression type,
and tick the boxes for ‘Display Equation on chart’ and ‘Display R-squared value on chart’. As a
general rule, do not ‘Set Intercept’ to a specific number (i.e., do not force the origin to be part of
the trendline). Do you know what the R-squared value really means? If not, you should look it up!!
Look at the equation for the trendline and think about it. What does the slope mean? What does the
y-intercept mean? What does this R-squared value mean?
Adding Error Bars to the Data Points on a Graph
Error bars represent the uncertainty of the measurement of the data. Do you have uncertainty? Do
you have uncertainty in both quantities (horizontal and vertical)? Is the uncertainty the same for all
data points, or does it change with the value of the quantity plotted? Wherever you have
uncertainty, you must have error bars! Once you have determined the amount of uncertainty, you
can add error bars by clicking on the ‘Layout’ tab (inside the ‘Chart Tools’ tab) and clicking on the
7
‘Error Bars’ icon. To get both horizontal and vertical error bars, a good choice is the ‘Error Bars
with Standard Error,’ and then click on the vertical and horizontal bars (individually) and right
click to ‘Format Error Bars.’ Apply the error bars according to the decisions that you have made
about your uncertainty. To remove either vertical or horizontal error bars, left-click on the bars,
then right- click and choose ‘Delete.’ (Future Lab Skill Goal: How do we propagate uncertainty
into calculated quantities?)
Other Cool Tools
As a spreadsheet program, Excel can do a lot of useful statistical analysis and mathematical
manipulation. Some functions you will find useful include: Sum, Average, and Standard
Deviation.
To Sum elements, click on the cell in which you wish the result to appear, then enter ‘=sum.’
Immediately, Excel will list possible formula options that begin with ‘sum’. For a simple
summation of elements, you would choose the SUM function, which you have already typed, but
you should note the other options available. Open parentheses following your sum, so that you
have now types ‘=sum(‘ and then highlight the cells to be summed. Close your parentheses and
press ‘Enter.’ (It would look like this ‘=sum(A1:A5)’ for a sum of the cells in rows 1 through 5 of
column A.)
To Average elements, click on the cell in which you wish the result to appear, and then enter
‘=average(.’ Highlight the cells you wish to average and then close your parentheses and press
‘Enter.’ Note that Excel will offer you a list of average functions—the most commonly used option
is the AVERAGE function. (It would look like this ‘=average(A1:A5)’ for the average of the cells
in rows 1 through 5 of column A.)
To perform a Standard Deviation on a set of elements, click on the cell in which you wish the
result to appear, and then enter ‘=stdev(.’ Highlight the cells you wish to perform a standard
deviation upon and then close your parentheses and press ‘Enter.’ Note that Excel will offer you a
list of standard deviation functions—the most commonly used option is the STDEV function.