Analyzing data using Scatter Charts

A ball is dropped from a tall building. The equation for a free falling object is:

where: y = position

y0 = initial position (ft)

v0 = initial velocity (ft/s)

a = acceleration due to gravity (32.2 ft/s2)

t = time (s)

Since the ball is dropped the initial velocity is zero and the equation becomes:

You perform the experiment and measure the time it takes for the ball to reach several positions along its fall.

The measured data is as follows:

Position (ft)
200 / 100 / 50 / 25 / 0

Test 1

/ 2.52 / 3.54 / 3.91 / 4.11 / 4.30
Test 2 / 2.43 / 3.47 / 3.94 / 4.13 / 4.31
Test 3 / 2.47 / 3.50 / 3.93 / 4.13 / 4.33
Average
Theoretical
  1. Using MS Excel, create a table with the measured values.
  2. Calculate the average for each position using the AVERAGE function.
  3. Calculate the theoretical value for each position. The theoretical value is found by solving the equation given above for time (t). Solving for t, we get the following formula:

Inserting know values for initial position and acceleration, we get the following:

You can use the SQRT function in Excel for this calculation.

  1. Create a graph of position versus time for each of the three tests and the average. Include a 2nd degree polynomial trend line for each and display the equation for the trend line.
  1. Create a graph of the theoretical values. Include a 2nd degree polynomial trend line and display the equation for the trend line.

NOTE: All charts should have a maximum y-axis value of 300 and a minimum of 0 and a maximum x-axis value of 5 and a minimum of 0.

HINT: Create the first chart and create a copy. Then, rename the sheet. Finally, change the data range of the X-values and the chart title for the copy. This will speed up the process considerably.

  1. Print your data table and the five charts. Staple the pages together in the following order: Data, Test 1, Test 2, Test 3, Average, Theoretical. Submit the pages to your instructor for grading.