An Introduction to Excel

for

PHY-105 Laboratory Investigations

Presented by Dr. Fred Jaquin

Revised 17 January 2016

The purpose of this investigation is to sharpen the student’s skill in using Microsoft’s Excel spreadsheetin PHY-105 laboratory analysis and reports. This purpose will be accomplished by leading the students in the following exercises related to the simulated constant acceleration motion of an object:

  1. Data entry and formatting,
  2. Data manipulation using student constructed and Excel- embedded formulas,
  3. Creation of a properly formatted graph with a trend line,
  4. Importing Excel data and graphs into a Word document, and
  5. Addressing student questions as they arise.

Introduction

Introduce Excel in a paragraph….

An object that moves under a constant acceleration can be described mathematically by a few simple relations. First, let’s review a few definitions.

  1. The position x of an object is a quantitative expression of where the object is located relative to some reference frame at a precise moment in time. Position always has units of length.
  2. The displacement Δx of an object is its change in position during a time interval; where the subscripts indicate the initial and final times of the interval. The symbol Δ stands for “change” and always represents the “final minus initial” values of the parameter changing. Displacement always has units of length.
  3. The velocity v of an object is the time rate of change of the object’s displacement; or in the notation of calculus. Velocity always has units of length over time.
  4. The acceleration a of an object is the time rate of change of the object’s velocity; or in the notation of calculus. Acceleration always has units of length over time squared.

For today’s purpose, the governing equations of an object subject to a constant acceleration are

Note that Eqn. 1 implies that a graph of position versus time ,unde the assumption of constant acceleration, will be

  • quadratic in time
  • with a “x-intercept” (i.e. t = 0) equal to the initial position of the object and
  • the coefficient of the linear t term is the initial velocity and
  • the coefficient of the quadratic t2 term is one-half the acceleration.

Further Eqn. 2 implies that a graph of velocity versus time is

  • linear in time (e.g.)
  • with a “v-intercept” (i.e. t = 0) equal to the initial velocity of the object and
  • theslope of v versus t is the acceleration.

PHY-105L Data for the Excel Investigation

Motion of an Object

Tic # / Position, cm
0 / 12.00
1 / 14.97
2 / 17.87
3 / 20.71
4 / 23.48
5 / 26.18
6 / 28.82
7 / 31.40
8 / 33.91
9 / 36.35
10 / 38.73
11 / 41.05
12 / 43.30
13 / 45.48
Tic # / Position, cm
14 / 47.60
15 / 49.65
16 / 51.64
17 / 53.56
18 / 55.42
19 / 57.21
20 / 58.94
21 / 60.60
22 / 62.19
23 / 63.72
24 / 65.19
25 / 66.59
26 / 67.92
27 / 69.19
Tic # / Position, cm
28 / 70.39
29 / 71.53
30 / 72.61
31 / 73.61
32 / 74.56
33 / 75.43
34 / 76.25
35 / 76.99
36 / 77.67
37 / 78.29
38 / 78.84
39 / 79.32
40 / 79.74

Lab Procedure: Following the instructor’s guide perform the following tasks:

  1. Open an Excel worksheet and enter the data listed above in two long columns. Insert a blank column between the Tic# and Position columns. Title the blank column “Time, s” and calculate the time associated with each tic assuming there are 20 tics per second.
  2. Create an appropriately formatted graph of position versus time. Add a quadratic trend line showing the equation and the correlation coefficient, R2.
  3. Interpret this graph under the assumption of constant acceleration.
  4. In two new columns calculate the velocity of the object, using the formula presented in the Introduction under definitions. Note the time associate with each velocity is best attributed to the time mid-way between the initial and final times of the interval (i.e. the average of the two times).
  5. Create an appropriately formatted graph of velocity versus time. Add a linear trend line showing the equation and the correlation coefficient, R2.
  6. Interpret this graph under the assumption of constant acceleration.
  7. In two new columns calculate the acceleration of the object, using the formula presented in the Introduction under definitions. Note the time associated with each acceleration value is best attributed to the time mid-way between the initial and final times of the interval (i.e. the average of the two times).
  8. Create an appropriately formatted graph of acceleration versus time. Add a linear trend line showing the equation and the correlation coefficient, R2.
  9. Interpret this graph under the assumption of constant acceleration.
  10. Following the instructor’s guide, insert a column to the right of the position column and create another list of positions that is contaminated by simulated random errors. Review the three graphs after these errors have been introduced. Discuss the effect of measurement errors on the appearance of each graph and its correlation coefficient.

Report Guidelines: This laboratory investigation report is due in five days (e.g. the Tuesday after a Thursday lab period). You may place your completed report in my office mailbox in the department office in F352.

The report must be structured as indicated below: