LAB 1: Working With Excel
Manual
PHYSICS
1
Introductory Physics, Lab 1, 2023W
LAB 1 PRE-LAB
Learning objectives for this lab:
1. Gain familiarity with the iOLab device and software.
2. Learn how to use Excel for quick calculations.
3. Use Excel to make a plot and fit a trendline to a set of data.
2
Introductory Physics, Lab 1, 2023W
LAB1: Pre-Lab Exercises (To be handed in.)
NOTE: The answers/results of this Pre-Lab will be used during your lab. Please keep a copy of it, or
take a picture of it with your phone and have it available for use during the lab.
1. When my lab group turns in our lab report at the end of today’s lab, we will
have automatic deductions taken off the report if we do not include four
specific things we are required to have on every report. Those four things are:
1. No student names
2. No Date
3. No iOLab number
4.
No Titile
2. In the excel worksheet shown, I have made
eight measurements of a quantity and entered
the eight measured values in column D, filling
cells D1 through D8. I would like to know the
average (or mean) of these eight cells. I would
like the average to be shown in cell D10 which
is boxed in the worksheet shown. To calculate
the average of those eight cells, what
command EXACTLY must I type in cell D10 to
compute the average of those eight cells?
= AVERAGE(D1:D8)
3
Introductory Physics, Lab 1, 2023W
3. If I have made a plot of X vs. Y data, and I want to know if y = x 2 , what are the
three ways I can use my graph and a trendline to do this?
(3.1)
Create Trendline - Power - Show Equation on the Chart - Show R squared value on the Chart
(3.2)
Plot y vs x squared - Check if linear
(3.3)
Create Trendline - Polynomial - Order 2 - Show equation on chart - Dipslay R squared on the chart
4
Introductory Physics, Lab 1, 2023W
NOTE ON USING THE LAB MANUALS
These lab manuals have been constructed with embedded YouTube videos to
better show you how certain aspects of data taking or analysis are performed.
You should definitely watch these before attempting the experiments and asking
your GA/TA for assistance.
Embedded YouTube videos are generally
shown as images with a magenta/pink box
around them and a magenta/pink
“YouTube” tag next to that. Clicking on this
link in our lab manual should take you to
the appropriate video if your device is
connected to the internet.
NOTE ON PASTING IMAGES INTO LAB REPORTS
As explained in the lab documents, students are required to paste or upload
images of graphs and other data. It is the students’ responsibility to crop
and/or zoom the image so that the data, graphs, etc. are clearly legible for the
GA or TA grading the report. Grades will be deducted for images that are not
clearly legible. If you are unsure of the legibility of your pasted image, please
consult with your GA or TA.
5
Introductory Physics, Lab 1, 2023W
LAB 1: WORKING WITH EXCEL
Microsoft Excel (or any of a variety of other types of software programs called
“spreadsheets”) can be used in a number of very useful ways by scientists and
engineers to analyze data sets. It can perform many different calculations quickly
and easily, it can be used to organize large amounts of data, and it can be used to
create graphs of your data which can then be analyzed with a variety of curve-
fitting analyses. In the introductory physics labs, you will be making extensive use
of Excel, so knowing how to manipulate it is a very necessary skill.
Excel uses a grid of cells to organize data into numbered rows and lettered
columns. This makes it easy to do calculations because you can reference specific
cells in your calculation (i.e. A1, A2, B1, B2, etc.).
Lettered columns
Numbered rows
For example, see the image below for how to find the average and standard
deviation of a set of data in Excel. These examples were presented in the first
week’s lab and short videos of how this are done are accessed by clicking on the
pictures.
6
Introductory Physics, Lab 1, 2023W
How to average a How to find the standard
column of data in deviation of a column of
Excel. See the data in Excel. See the
formatting of the formatting of the
equation in cell equation in cell A9.
A7.
To perform any calculation in Excel, you must go to an empty cell and begin with
the equal sign (=). Everything that follows an equal sign is not a number or data
point. After the equal sign, you can either enter a function (such as “average” and
“stdev” as seen in the image above), a calculation (such as “A1+A2”, “A1*A2”,
“15*A1”, “B2-A2”, “(A1+A2)*B3”, etc.), or some combination of the two. When
you are done entering your fucntion or calculation pressing “Enter” finishes the
command and moves you to the next row.
In the example below, the average of cells A1 through A6 is determined in three
different ways:
cell references
constant
function
Determining the average using the "average" Determining the average using a calculation Determining the average using the
function "sum" function and then dividing by 6
7
Introductory Physics, Lab 1, 2023W
Lastly, the real power of Excel comes from usually only having to type in a
function or calculation once. Clicking and dragging the cell in which that is typed
will “copy and paste” that function or calculation but with the cell entries
automatically updated to refer to the new row or column in which it is pasted.
For example, if you wanted to create a column of numbers 1-10 in
column A you could type out by hand, 1 (enter), 2(enter), 3 (enter),
etc. This is very slow and if you needed numbers from 1 to 100, it
would take forever.
A MUCH better way to do this is to type “1” in A1, then “enter”
which moves you to A2. Type “=” and then either type “A1+1” or
click in cell A1 and type “+1”. Hitting enter then moves you to A3
and the number 2 appears in A2 (because A2 equals A1+1 = 1+1 =
2). Lastly click back in cell A2 and click and hold the small square in
the lower right-hand corner of the cell. While holding, drag the box
to copy and past that calculation into as may cells as you want.
2. Click in A2 and click and hold the small box in the
lower corner of the cell. Drag the box to fill all the
1. Type 1 in A1 and a calculation in A2. Hit Enter. cells you want to copy that calculation into and
then release the mouse button.
8
Introductory Physics, Lab 1, 2023W
Exercise 1, Creating data in Excel and fitting a curve to
it.
The goal of this exercise is to generate x and y data, plot x vs. y, and then
determine the relationship between x and y by fitting a curve to the data.
Open an Excel sheet on your own laptop or the lab computer.
ACTION
Generate two columns of data. The first should start at “1” and increment by 10
every cell (i.e. 1, 11, 21, 31, etc.) This is the “X” data.
In the second column, calculate the square of the cell in the first column (in Excel
and most programming languages, the “^” means square, so to square cell A1,
you type “=A1^2”). This is the “Y” data.
Make each column 30 cells long, so the first column should go from 1 to 291. Use
calculations in the cells to do this, and the copy and drag pasting described
earlier. This entire action should take no more than 30 seconds, demonstrating
the power of using the Excel functions. If it takes longer than that, you may be
doing it incorrectly.
Plot the X data vs. the Y data using
a “scatter plot.” When you are
done you should have something
that looks like the figure at right.
If you do not know how to plot
your data, click in the image to
watch a short video explaining
how graphs are made.
Add a chart title and label both
axes.
(NOTE: On a Mac, all these commands you need to use and
the appearance of Excel may be different. All examples are
given using the Windows OS.)
9
Introductory Physics, Lab 1, 2023W
ACTION
We would like to determine how the Y data depends on the X data (even though
we already know that y = x 2 .)
To do this insert a trendline to the data. The previous video shows how to do this
starting at 2:33.
For this trendline, a “linear” line will not work. We need to use
something which has the correct mathematical relationship.
So we can try different relationships: Exponential, Logarithmic,
etc. Because our Y data is x-squared, we can use one of two
functions to show this: a “Polynomial” of Order 2 or the
“Power” function. The Power function will give you y = x power ,
and it will tell you what the power is (yours should be 2).
The Polynomial of order 2 means that x2 is the largest term,
but there is also an x term and a constant term. This will give
you an equation of y = Ax2 + Bx + C and it will tell you what A,
B, and C are. Because your data is purely x2, the values of B
and C should be almost zero.
For all of your curve fitting, always make sure you select “Display Equation on
Chart” and “Display R-squared value on Chart.” The R-squared (R2) value tells you
how “good” the fit is or how closely your data and the curve agree. An R2 of 1 is a
perfect fit of your equation to the data, so R2 near 1 is good.
REPORT 1.1
Submit two graphs showing the data you generated and your trendline fits. In
one graph you should use a polynomial of order two and in the other graph you
should use the power function. By showing the displayed equation (make sure it
is legible) you should be able to show that your data is well-fit by the function you
chose. In this case, both the polynomial and the power will work equally well.
To easily generate the two graphs, after you have fit your first trendline and are
happy with your graph, you can simply right click in the graph and “copy” it. Right
click anywhere else in a blank cell of the spreadsheet and “paste” it. In the pasted
10
Introductory Physics, Lab 1, 2023W
graph, you can delete the trendline and the equation, and start over with your
second function. This way you do not need to do double the work of adding axis
labels, formatting, etc.
Submit a screenshot of your Excel data clearly showing the X and Y data. Click in
one of the cells in the Y data so that your calculation can be seen.
If you want to, one screenshot showing the Excel data and both graphs all in one
image could be generated and submitted.
Make sure to NOT use the default blue colors for data and trendline. Change
them both to make them very visible to your grader.
Make sure you clearly explain what it is you are doing in this exercise.
ACTION
There is one more way to show that for your data y = x 2 . Because the graph you
made is plotting X vs. Y, it curves upwards because the Y data increases as X-
squared. So if you plot the Y-data not versus X, but versus X-squared, that curve
will NOT curve up but be a straight line. (Similarly if your data was y = x3 if you
plotted Y versus X-cubed it would also be a straight line; if your data was y = 1x
plotting Y versus one over X would be a straight line).
Use your original data and generate another column of X-squared data and plot Y
versus this X-squared data and fit a trendline to it. This should now be very linear.
REPORT 1.2
Submit a third graph showing the data you generated and your trendline fit.
Make sure the equation of the line is shown and make sure you label every axis.
In this case the x-axis should read something like “X-squared” so it is obvious why
the data is now linear.
At the end of exercise 1, you will have learned how to generate data and proven
that the two columns of data are related by y = x 2 in three different ways.
11
Introductory Physics, Lab 1, 2023W
Exercise 2, Analyzing unknown data in Excel
ACTION
Download from Brightspace the spreadsheet of data prepared for you, “Lab 1
unknown data”. In this data we have recorded the strength of a magnetic fringe
field from the side of an MRI machine (in milli-Tesla, or mT) versus the distance
away from the MRI machine (in cm.)
Make a plot of this data with the distance as the X-data and magnetic field as the
Y-data. Re-scale your axes so the data is clearly visible, large, and centered in the
graph. Assign new colors to the data. Label every axis and give your graph a title.
The goal of this exercise is to determine how the magnetic field drops off as you
move away from the machine.
Using what you learned in exercise 1, use various trendlines to determine the
functional behavior. The correct trendline will be either a polynomial of order 2, a
line, or a power, and the one with the best R-squared value can be taken to be
your best estimate of the correct function.
REPORT 2.1
Submit the data and three graphs in your lab report: one showing a linear fit, one
showing a polynomial fit of order 2, and one showing a power law fit. Make sure
the graphs are very legible, that you have scaled the graphs well, and that the
equations and R-squared are shown. Clearly state which fit you feel is best and
why.
Lastly, as was done in Exercise 1, plot your Y-data versus X to some integer power
determined from the best fit and fit a linear trendline to this data showing how
this functional form makes the graph linear. In a power fit, always assume the
exponent is an integer unless otherwise told, so if your power law fit returned
y = x −3.9 for example, you would NOT use 1 3.9 , but rather 1 4 , rounding the
x x
value obtained by the fit (-3.9) to the nearest integer (-4).
12
Introductory Physics, Lab 1, 2023W
Exercise 3, Generating data with the iOLab and
Analyzing it in Excel
In Exercises 3, you will be using the iOLab wheels sensor to generate data. Before
beginning, you may want to watch this video to remind yourself how you can
rescale your data to make it look beautiful for inclusion in your lab report. (Note:
Mats is NOT at the University of Windsor! This is a video from a different
university.)
Watch this video to remind yourself how to take “snapshots” of your data and
upload them to the iOLab cloud so you can include them in your lab report. You
can also take screen captures using alt+print screen or a snipping tool if you are
not “sharing” your data.
13
Introductory Physics, Lab 1, 2023W
In this experiment, you will be attempting to push the iOLab device the same
distance 10 times and you will learn how to use Excel to analyze this data. It does
not really matter what the distance is and something around 50-100 cm should
work.
Watch the following video to see how to set up this experiment and acquire data.
ACTION
Open the iOLab software and activate the Wheel sensor.
Use two reference points for attempting to push the iOLab the same distance for
each trial. Click “Record” and give the iOLab a push, then click “Stop”. This
constitutes the first trial. Analyze and record the data from this first trial before
proceeding with your second trial. Read below for how to analyze the data.
In an Excel sheet, you will need to record ry, initial, ry, final, and the area under the
velocity curve for that attempt. To do this, highlight a section covering the peak on
the Velocity-Time graph. A suitable region to highlight is shown in the images
below. Note that in these images, the 4th trial is being analyzed. You should
analyze each trial and have Excel perform the appropriate calculations BEFORE
proceeding to the next trial. This is to ensure that you are acquiring good data. In
this case, “good data” means that ry, final – ry, initial is the same or very close to the
value for the area under the velocity curve.
14
Introductory Physics, Lab 1, 2023W
The cursor is placed at the start of the highlighted region and ry,initial = 1.185 m.
The area under the velocity curve reads 0.457 m. Note how ALL of the velocity
curve data from trial four is included in the highlighted region.
The cursor is placed at the end of the highlighted region and ry,final = 1.643 m.
Watch the following videos to see how
to analyze the data and set up your
Excel sheet.
15
Introductory Physics, Lab 1, 2023W
REPORT 3.1
After describing your experiment, include at least one screen capture in your
report clearly showing your highlighted region with the cursor depicting ry,initial and
also ry,final for one good trial. Make sure the measurement of the area under the
velocity curve is also visible in the screenshot.
ACTION
Obtain your second trial by clicking “Continue,” giving the iOLab a push, then
clicking “Stop.” Analyze and record the data in Excel from this second trial.
Repeat this 8 more times so that you have a total of 10 trials.
ACTION
In Excel, calculate the average and standard deviation of the difference ( ry, final –
ry, initial ) and also of the area under the velocity curve for the 10 trials.
ACTION
In Excel, make a plot of the area under the velocity curve as a function of ry, final –
ry, initial. Fit a linear trendline to the data in this plot and display the equation and
R2 value on the plot. Include a plot title and titles for the x-axis and y-axis. How
to do this is all shown in the video above.
In theory, ry, final – ry, initial and the area under the velocity curve should be equal.
This would give a y = x relation, meaning that the slope of this line is 1. Your
16
Introductory Physics, Lab 1, 2023W
values may not be exactly equal, but they should be close, resulting in a value of
the slope that is close to 1.
REPORT 3.2
Include a screen capture of your Excel sheet data file clearly showing your
numeric data from all 10 trials for:
• ry, initial
• ry, final
• ry, final – ry, initial
• area under the velocity curve
• difference between area under the velocity curve and ry, final – ry, initial
• average and standard deviation of both your ry, final – ry, initial data and the
area under the velocity curve data
This can be done by highlighting all the cells, right click “copy”, then “paste” into
your report as an image or as a table.
Also include in your report your plot with the appropriate titles, the trendline,
equation, and R2 value displayed.
17
Introductory Physics, Lab 1, 2023W
