# 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