Graph Interpretation Exercises easy excel assignment ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,physics lab .all information in WORD FILE I posted. y o c u -t
Graph Interpretation Exercises easy excel assignment ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,physics lab .all information in WORD FILE I posted. y
o
c u -tr a c k
.c
.d o
Morgan Extra Pages
Graphing with Excel
to be carried out in a computer lab, 3rd floor Calloway Hall or elsewhere
Name Box
Figure 1. Parts of an Excel spreadsheet.
The Excel spreadsheet consists of vertical columns and horizontal rows; a column and row intersect
at a cell. A cell can contain data for use in calculations of all sorts. The Name Box shows the currently selected cell (Fig. 1).
In the Excel 2007 and 2010 versions the drop-down menus familiar in most software screens have
been replaced by tabs with horizontally-arranged command buttons of various categories (Fig. 2)
Figure 2. Tabs.
___________________________________________________________________
Open Excel, click on the Microsoft circle, upper left, and Save As your surname.xlsx on the desktop. Before leaving
the lab e-mail the file to yourself and/or save
to a flash drive. Also e-mail it to your instructor.
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
er
F-
c u -tr a c k
.c
y
o
c u -tr a c k
.c
EXERCISE 1: BASIC OPERATIONS
Click Save often as you work.
1. Type the heading “Edge Length” in Cell
A1 and double click the crack between
the A and B column heading for automatic widening of column A. Similarly,
write headings for columns B and C and
enter numbers in Cells A2 and A3 as in
Fig. 3. Highlight Cells A2 and A3 by
dragging the cursor (chunky plus-shape)
over the two of them and letting go.
2. Note that there are three types of cursor
crosses: chunky for selecting, barbed for
moving entries or blocks of entries from
cell to cell, and tiny (appearing only at
the little square in the lower-right corner
of a cell). Obtain a tiny arrow for Cell
A3 and perform a plus-drag down Column A until the cells are filled up to 40
(in Cell A8). Note that the two highlighted cells set both the starting value of
the fill and the intervals.
Figure 4. A formula.
5. Highlight Cells B2 and C2; plus-drag
down to Row 8 (Fig. 5). Do the numbers look correct?
Click on some cells in the newly filled
area and notice how Excel steps the row
designations as it moves down the column (it can do it for horizontal plusdrags along rows also). This is the major programming development that has
led to the popularity of spreadsheets.
Figure 3. Entries.
3. Click on Cell B2 and enter a formula for
face area of a cube as follows: type =,
click on Cell A2, type ^2, and press Enter (note the formula bar in Fig. 4).
4. Enter the formula for cube volume in
Cell C2 (same procedure, but “=, click
on A2, ^3, Enter”).
Figure 5. Plus-dragging formulas.
.d o
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
er
F-
c u -tr a c k
.c
y
o
c u -tr a c k
.c
Figure 6. Creating a scatter graph.
6. Now let’s graph the Face Area versus
Edge Length: select Cells A1 through
B8, choose the Insert tab, and click the
Scatter drop-down menu and select
“Scatter with only Markers” (Fig. 6).
7. Move the graph (Excel calls it a “chart”)
that appears up alongside your number
table and dress it up as follows:
a. Note that some Chart Layouts have
appeared above. Click Layout 1 and
alter each title to read Face Area for
the vertical axis, Edge Length for the
horizontal and Face Area vs. Edge
Length for the Graph Title.
b. Activate the Excel Least squares
routine, called “fitting a trendline” in
the program: right click any of the
data markers and click Add Trendline. Choose Power and also check
“Display equation on chart” and
“Display R-squared value on chart.”
Fig. 7 shows what the graph will
look like at this point.
c. The titles are explicit, so the legend
is unnecessary. Click on it and press
the delete button to remove it.
Figure 7. A graph with a fitted curve.
.d o
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
er
F-
c u -tr a c k
.c
y
o
c u -tr a c k
.c
8. Now let’s overlay the Volume vs. Edge Length curve onto the same graph (optional for
203L/205L): Make a copy of your graph by clicking on the outer white area, clicking ctrl-c
(or right click, copy), and pasting the copy somewhere else (ctrl-v). If you wish, delete the
trendline as in Fig. 8.
a. Right click on the outer white space, choose Select Data and click the Add button.
b. You can type in the cell ranges by hand in the dialog box that comes up, but it is easier to
click the red, white, and blue button on the right of each space and highlight what you
want to go in. Click the red, white, and blue of the bar that has appeared, and you will
bounce back to the Add dialog box. Use the Edge Length column for the x’s and Volume
for the y’s.
c. Right-click on any volume data point and choose Format Data Series. Clicking Secondary Axis will place its scale on the right of the graph as in Fig. 8.
d. Dress up your graph with two axis titles (Layout-Labels-Axis Titles), etc.
Figure 8. Adding a second curve and
y-axis to the graph
.d o
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
er
F-
c u -tr a c k
.c
y
o
c u -tr a c k
.c
EXERCISE 2: INTERPRETING A LINEAR GRAPH
Introduction: Many experiments are repeated a number of times with one of the
parameters involved varied from run to run.
Often the goal is to measure the rate of
change of a dependent variable, rather than a
particular value. If the dependent variable
can be expressed as a linear function of the
independent parameter, then the slope and yintercept of an appropriate graph will give
the rate of change and a particular value,
respectively.
An example of such an experiment in
PHYS.203L/205L is the first part of Lab 20,
in which weights are added to the bottom of
a suspended spring (Figure 9).
ing weights in newtons of 0.49, 0.98, etc.
The weight pan was used as the pointer for
reading y and had a mass of 50 g, so yo could
not be directly measured. For convenient
graphing Equation 1 can be rewritten:
-(Mg) = – ky + kyo
Or
(Mg) = ky – kyo
(Eq. 1′)
Procedure
1. On your spreadsheet note the tabs at the
bottom left and double-click Sheet1.
Type in “Basics,” and then click the
Sheet2 tab to bring up a fresh worksheet.
Change the sheet name to “Linear Fit”
and fill in data as in this table.
Hooke’s Law
Experiment
y (m)
-Fs = Mg (N)
0.337
0.49
0.388
0.98
0.446
1.47
0.498
1.96
0.550
2.45
Figure 9. A spring with a weight
stretching it
This experiment shows that a spring exerts a
force Fs proportional to the distance
stretched y = (y-yo), a relationship known
as Hooke’s Law:
Fs = – k(y – yo)
(Eq. 1)
where k is called the Hooke’s Law constant.
The minus sign shows that the spring opposes any push or pull on it. In Lab 20 Fs is
equal to (- Mg) and y is given by the reading
on a meter stick. Masses were added to the
bottom of the spring in 50-g increments giv-
2. Highlight the cells with the numbers,
and graph (Mg) versus y as in Steps 6
and 7 of the Basics section. Your
Trendline this time will be Linear of
course.
If you are having trouble remembering
what’s versus what, “y” looks like “v”,
so what comes before the “v” of “versus”
goes on the y (vertical) axis. Yes, this
graph is confusing: the horizontal (“x”)
axis is distance y, and the “y” axis is
something else.
3. Click on the Equation/R2 box on the
graph and highlight just the slope, that
is, only the number that comes before the
“x.” Copy it (control-c is a fast way to
.d o
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
er
F-
c u -tr a c k
.c
y
o
c u -tr a c k
.c
do it) and paste it (control-v) into an
empty cell. Do likewise for the intercept
(including the minus sign).
SAVE
YOUR FILE!
5. The next steps use the standard procedure for obtaining information from linear data. Write the general equation for
a straight line immediately below a
hand-written copy of Equation 1′ then
circle matching items:
(Mg) = k y + (- k yo)
y
= mx+ b
(Eq. 1′)
Note the parentheses around the intercept term of Equation 1′ to emphasize
that the minus sign is part of it.
Equating above and below, you can create two useful new equations:
slope m = k
(Eq. 2)
y-intercept b = -kyo
(Eq. 3)
6. Solve Equation 2 for k, that is, rewrite
left to right. Then substitute the value
for slope m from your graph, and you
have an experimental value for the
Hooke’s Law constant k. Next solve
Equation 3 for yo, substitute the value for
intercept b from your graph and the value of k that you just found, and calculate
y o.
7. Examine your linear graph for clues to
finding the units of the slope and the yintercept. Use these units to find the
units of k and yo.
8. Present your values of k and yo with their
units neatly at the bottom of your
spreadsheet.
9. R2 in Excel, like r in our lab manual and
Corr. in the LoggerPro software, is a
measure of how well the calculated line
matches the data points. 1.00 would indicate a perfect match. State how good a
match you think was made in this case?
10. Do the Homework, Further Exercises on
Interpreting Linear Graphs, on the following pages.
.d o
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
er
F-
c u -tr a c k
.c
y
o
.c
Morgan Extra Pages
Homework: Graph Interpretation Exercises
EXAMPLE WITH COMPLETE SOLUTION
In PHYS.203L and 205L we do Lab 9 Newton’s
Second Law on Atwood’s Machine using a photogate
sensor (Fig. 1). The Atwood’s apparatus can slow the
rate of fall enough to be measured even with primitive
timing devices. In our experiment LoggerPro software
automatically collects and analyzes the data giving
reliable measurements of g, the acceleration of gravity.
The equation governing motion for Atwood’s
Machine can be written:
g
f
a m
M
M
Eq.1
where a is the acceleration of the masses and string, g is
the acceleration of gravity, M is the total mass at both
ends of the string, m is the difference between the
masses, and f is the frictional force at the hub of the
pulley wheel.
In this exercise you are given a graph of a vs. m
obtained in this experiment with the values of M and the
slope and intercept (Fig. 2). The goal is to extract values
for acceleration of gravity g and frictional force f from
this information.
To analyze the graph we write y = mx + b, the
general equation for a straight line, directly under
Equation 1 and match up the various parameters:
Figure 1. The Atwood’s Machine
setup (from the LoggerPro handout).
g
f
a m
M
M
y m
x b
Atwood’s Machine
M = 0.400 kg
a = 24.4 m – 0.018
2
R = 0.998
g
slope, m
M
( Eq. 2)
1.40
1.20
1.00
0.80
0.60
0.40
0.20
0.00
0.000
0.010
0.020
0.030
0.040
0.050
0.060
m (kg)
Figure 2. Graph of acceleration versus mass
difference; data from a Physics I experiment.
and
f
b
M
2
Equating above and below, you can create two new
equations:
a (m/s )
c u -tr a c k
(Eq. 3)
.d o
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
er
F-
c u -tr a c k
.c
y
o
.c
To handle Equation 2 it pays to consider
what the units of the slope are. A slope is “the
rise over the run,“ so its units must be the units
of the vertical axis divided by those of the
horizontal axis. In this case:
Exp. Acc.
100
Acc.
9.76 9.80
100
9.80
0. 4%
% Error
m / s2
m
kg
kg s 2
Now let’s solve Equation 2 for g and
substitute the values of total mass M and of the
slope m from the graph:
g Mm
0.400kg 24.4m /( kg s 2 )
A similar process with Equation 3 leads
to a value for f, the frictional force at the hub
of the pulley wheel. Note that the units of
intercept b are simply whatever the vertical
axis units are, m/s2 in this case. Solving
Equation 3 for f:
9.76 m / s 2
f Mb
0.400 kg ( 0.018 m / s 2 )
Using 9.80 m/s2 as the Baltimore accepted
value for g, we can calculate the percent error:
7.2 10 3 kg m / s 2 7.2 mN
EXERCISE 1
The Picket Fence experiment makes use of
LoggerPro software to calculate velocities at
regular time intervals as the striped plate passes
through the photogate (Fig. 3). The theoretical
equation is
v = vi + at
(Eq. 4)
Picket Fence Drop
v (m/s)
c u -tr a c k
12
10
8
6
4
2
0
y = 9.8224x + 0.0007
R2 = 0.9997
0
where vi = 0 (the fence is dropped from rest) and
a = g.
a. Write Equation 4 with y = mx + b
under it and circle matching factors as in
the Example.
b. What is the experimental value of the
acceleration of gravity? What is its
percent error from the accepted value for
Baltimore, 9.80 m/s2?
c. Does the value of the y-intercept make
sense?
d. How well did the straight Trendline
match the data?
0.2
0.4
0.6
0.8
1
1.2
t (s)
Figure 3. Graph of speed versus time as
calculated by LoggerPro as a picket fence
falls freely through a photogate.
.d o
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
er
F-
c u -tr a c k
.c
y
o
.c
EXERCISE 2
This is an electrical example from
PHYS.204L/206L, potential difference, V,
versus current, I (Fig. 4). The theoretical
equation is
V = IR
(Eq. 5)
Ohm’s Law
Potential difference,
V (V)
0.4
y = 0.628x – 0.0275
2
R = 0.9933
0.3
0.2
0.1
0
0
and is known as “Ohm’s Law.” The unit
symbols stand for volts, V, and Amperes, A.
The factor R stands for resistance and is
measured in units of ohms, symbol (capital
omega). The definition of the ohm is:
V
0.1
0.2
0.3
0.4
0.5
0.6
Current, I (A)
Figure 4. Graph of potential difference versus
current; data from a Physics II experiment.
The theoretical equation, V = IR, is known
as “Ohm’s Law.”
(Eq. 6)
By coincidence the letter symbols for
potential (a quantity ) and volts (its unit) are
identical. Thus “voltage” has become the
laboratory slang name for potential.
a. Rearrange the Ohm’s Law equation to
match y = mx + b..
b. What is the experimental resistance?
c. Comment on the experimental intercept:
is its value reasonable?
EXERCISE 3
Current versus (1/Resistance)
This graph (Fig. 5) also follows Ohm’s Law,
but solved for current I. For this graph the
experimenter held potential difference V
constant at 15.0V and measured the current for
resistances of 100, 50, 40, and 30 Solve
Ohm’s Law for I and you will see that 1/R is the
logical variable to use on the x axis. For units,
someone once jokingly referred to a “reciprocal
ohm” as a “mho,” and the name stuck.
a. Rearrange Equation 5 solved for I to
match y = mx + b.
b. What is the experimental potential
difference?
c. Calculate the percent difference from the
15.0 V that the experimenter set on the
power supply (the instrument used for
such experiments).
d. Comment on the experimental intercept:
is its value reasonable?
I (milliamperes)
c u -tr a c k
600
500
400
300
200
100
0
y = 14.727x – 0.2214
R2 = 0.9938
5
10
15
20
R
-1
25
30
35
(millimhos)
Figure 5. Another application of Ohm’s Law:
a graph of current versus the inverse of
resistance, from a different electric circuit
experiment.
.d o
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
er
F-
c u -tr a c k
.c
y
o
.c
EXERCISE 4
The Atwood’s Machine experiment (see the
solved example above) can be done in another
way: keep mass difference m the same and vary
the total mass M (Fig. 6).
Atwood’s Machine
m = 0.020 kg
f = 7.2 mN
y = 0.1964x – 0.0735
2
R = 0.995
1.000
2
a (m/s )
a. Rewrite Equation 1 and factor out (1/M).
b. Equate the coefficient of (1/M) with the
experimental slope and solve for
acceleration of gravity g.
c. Substitute the values for slope, mass
difference, and frictional force and
calculate the experimental of g.
d. Derive the units of the slope and show
that the units of g come out as they
should.
e. Is the value of the experimental intercept
reasonable?
0.800
0.600
0.400
2.000
2.500
3.000
3.500 4.000
4.500
5.000
1/M (1/kg)
Figure 6. Graph of acceleration versus the
reciprocal of total mass; data from a another
Physics I experiment.
EXERCISE 5
Effect of Pendulum Length on Period
In the previous two exercises the reciprocal
of a variable was used to make the graph come
out linear. In this one the trick will be to use the
square root of a variable (Fig. 7).
In PHYS.203L and 205L Lab 19 The
Pendulum the theoretical equation is
T 2
L
g
( Eq . 7 )
where the period T is the time per cycle, L is the
length of the string, and g is the acceleration of
gravity.
a. Rewrite Equation 7 with the square root
of L factored out and placed at the end.
b. Equate the coefficient of √L with the
experimental slope and solve for
acceleration of gravity g.
2.400
2.000
T (s)
c u -tr a c k
y = 2.0523x – 0.0331
R2 = 0.999
1.600
1.200
0.800
0.400
0.00 0.10 0.20 0.30 0.40 0.50 0.60 0.70 0.80 0.90 1.00 1.10
L 1/2 (m1/2 )
Figure 7. Graph of period T versus the square
root of pendulum length; data from a Physics
I experiment.
c. Substitute the value for slope and
calculate the experimental of g.
d. Derive the units of the slope and show
that the units of g come out as they
should.
e. Is the value of the experimental intercept
reasonable?
.d o
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
er
F-
c u -tr a c k
.c
y
o
.c
EXERCISE 6
In Exercise 5 another approach would have
been to square both sides of Equation 7 and plot
T2 versus L. Lab 20 directs us to use that
alternative. It involves another case of periodic
or harmonic motion with a similar, but more
complicated, equation for the period:
M Cm s
k
( Eq . 8 )
where T is the period of the bobbing (Fig. 8), M
is the suspended mass, ms is the mass of the
spring, k is a measure of stiffness called the
spring constant, and C is a dimensionless factor
showing how much of the spring mass is
effectively bobbing.
a. Square both sides of Equation 8 and
rearrange it to match y = mx + b.
b. Write y = mx + b under your
rearranged equation and circle matching
factors as in the Example.
c. Write two new equations analogous to
Equations 2 and 3 in the Example. Use
the first of the two for calculating k and
the second for finding C from the data of
Fig. 9.
d. A theoretical analysis has shown that for
most springs C = 1/3. Find the percent
error from that value.
e. Derive the units of the slope and
intercept; show that the units of k come
out as N/m and that C is dimensionless.
Figure 8. In Lab 20 mass M is suspended
from a spring which is set to bobbing up and
down, a good approximation to simple
harmonic motion (SHM), described by
Equation 8.
Lab 20: SHM of a Spring
Mass of the spring, m s = 25.1 g
2
T 2
T2
c u -tr a c k
1.0000
0.8000
0.6000
0.4000
0.2000
0.0000
y = 3.0185x + 0.0197
R 2 = 0.9965
0
0.05
0.1
0.15
0.2
0.25
0.3
M ( k g)
Figure 9. Graph of the square of the
period T2 versus suspended mass M data
from a Physics I experiment.
.d o
m
o
w
w
w
.d o
C
lic
k
to
bu
y
bu
to
k
lic
C
w
w
w
N
O
W
!
h a n g e Vi
e
N
PD
!
XC
er
O
W
F-
w
m
h a n g e Vi
e
w
PD
XC
…
Purchase answer to see full
attachment
