Physics Laboratory 3

Solving physics problems with a spreadsheet

Objective

In this laboratory students will work with a data set which contains simulated data. The data set describes the motion of an object in two dimensions. It is in the form of a table, listing the time and the x- and y-coordinates of the object at that time.

t(s)	x(m)	y(m)
0	0	0
1	1	0
2	4	0
3	9	0
...	...	...

Student will study the motion of an object in two dimensions. Given the x- and y-coordinates of the object as a function of time, they will plot its trajectory. Students will write a small program to animate the object, changing its position as a function of time. They will then find the x- and y- components and the magnitude of the velocity and the acceleration, averaged over the time interval between successive data points, and plot them as a function of time. Students will also find the displacement of the object and estimate the total distance traveled by the object in the time interval for which data are given in the table.

Information about the data set

Data set

A blimp hovers high over a racetrack. A video camera is pointing straight down and records the motion of a single car around the track at 30 frames per second. The camera uses a wide-angle lens, so the entire track is mapped in each frame. When the video is played back, a coordinate system is overlaid onto the screen. The x- and y-coordinates of the car are measured every 30 frames, i.e. every second, and recorded in a table.

t(s)	x(m)	y(m)
0	0	0
1	1	0
2	4	0
3	9	0
...	...	...

Procedure

Use Microsoft Internet Explorer to view these instructions.

Click on the data set.

Highlight the entire contend of the file and copy it to the clipboard.

Open Microsoft Excel and paste the data set into sheet 1.

Place the cursor into cell A1.
Right-click and choose Paste Special, Unicode Text.
You now have a spreadsheet containing the data set in columns A-C.

A	B	C
t(s)	x(m)	y(m)
0	0	0
1	1	0
2	4	0
3	9	0
...	...	...

Note: Save your spreadsheet periodically.

If you are using another browser, the Copy and Paste commands may work as described above. Save the data set to your hard drive and then open the saved file it in Microsoft Excel.

Excel

Plot the trajectory of the object.

Highlight the columns (B and C) containing x and y.

Insert a chart into your spreadsheet.

	Choose the standard type XY (Scatter), and the sub-type Scatter. Give the chart the title "Trajectory" and label the axes. Click the legend and delete it.
	Place the chart as an object in sheet 2. (In Excel 2007, right-click the chart and choose to move it.) Position it in the upper-left corner of sheet 2.

Plot the x- and y- coordinate of the object as a function of time.

Highlight the columns (A-C) containing t, x, and y.

Insert a chart into your spreadsheet.

	Choose the standard type XY (Scatter), and the sub-type Scatter. Give the chart the title "Coordinates (t)" and label the axis. Choose to show a legend.
	Place the chart as an object in sheet 2 and position it under your first chart.
	Save your Excel workbook.

Word

	Open a new Microsoft Word Document. Leave page 1 blank, and paste your "Trajectory" and "Coordinates (t)" charts onto page 2.
	In a few sentences describe what you can learn about the motion of the car from these two charts.
	Save your Word document.

Excel

In Microsoft Excel, right click on your trajectory chart in sheet 2. Choose source data, and then click on the series tab. In Excel 2007, choose Select Data.

	Choose to add a series.
	Put the cursor in the text box labeled "X Values:" and click on the sheet 1 tab.
	Click on cell B2 of sheet 1.
	Put the cursor in the text box labeled "Y Values:" and click on the sheet 1 tab.
	Click on cell C2 of sheet 1 and then click OK.

Data series 2 appears as a single point in your chart. Right click on this point and choose "format data series". Increase the symbol size to 10 pts. (In Excel 2007, choose Marker Options, Build-in, Size 10.)

Right click on any point belonging to data series 1 and choose "format data series". Choose to display a line but no markers. (In Excel 2007, choose Marker Options, None, .Line Color, Solid line.)

On the tools menu choose "macro". (In Excel 2007, click the developer tab and choose macro. If the developer tab is not available, click the Microsoft Office Button

and then click Excel Options, Popular, and select the Show Developer tab in the Ribbon check box.) Type animation in the macro name textbox and choose to create a macro.

	A code window will open. Enter the following program code. (You can copy and paste.) Sub animation() Set w1 = Worksheets("sheet1") Set w2 = Worksheets("sheet2") Set ser2 = w2.ChartObjects(1).Chart.SeriesCollection(2) i = 2 Do While i < 100 b$ = "b" & i c$ = "c" & i ser2.XValues = w1.Range(b$) ser2.Values = w1.Range(c$) w1.Calculate i = i + 1 Loop End Sub
	Close the code window.

On the tools menu choose "macro" again. For your animation macro choose "Options" and define Ctrl+m as the shortcut key. Close the macro window.

On sheet 2, position your cursor in an empty cell, outside any chart. Click Ctrl+m and observe your animated trajectory chart.

Save your Workbook.

Word

	Go to your Word document and discuss if the animation confirms your previous description of the motion, which was based on the information obtained from the static "Trajectory" and "Coordinates (t)" charts.
	Can you run the animation backward by making changes in the code? If yes, include your changed code in your Word document.
	Save your Word document.

Excel

Find the x- and y-components and the magnitude of the velocity as a function of time

Produce columns E-H in your spreadsheet (sheet 1) as shown below.

E	F	G	H
t(s)	v_x(m/s)	v_y(m/s)	v(m/s)
0.50	1.00	0.00	1.00
1.50	3.00	0.00	3.00
2.50	5.00	0.00	5.00
...	...	...	...

Column E contains the average time for the data points in the same row and the data points in the next row of columns A-C.

[In cell E2 type =(A2+A3)/2. Then right-click the cell and choose Copy. Highlight the remaining cells in column E, E3 to the next to last cell with an entry in column A. Right-click and choose, Paste.]

Column F contains the x-component of the velocity, averaged over successive time intervals.

[In cell F2 type =(B3-B2)/(A3-A2). Then right-click the cell and choose Copy. Highlight the remaining cells in column F, F3 to the next to last cell with an entry in column A. Right-click and choose, Paste.]

Column G contains the y-component of the velocity, averaged over successive time intervals.

[In cell G2 type =(C3-C2)/(A3-A2). Then right-click the cell and choose Copy. Highlight the remaining cells in column G, G3 to the next to last cell with an entry in column A. Right-click and choose, Paste.]

Column H contains the magnitude of the velocity.

[In cell H2 type =SQRT(F2^2+G2^2). Then right-click the cell and choose Copy. Highlight the remaining cells in column H, H3 to the next to last cell with an entry in column A. Right-click and choose, Paste.]

Highlight all your newly created cells and click Format, Cells, Number, and choose 2 or 3 decimal places.

Plot the x- and y components and the magnitude of the velocity of the object as a function of time.

Highlight the columns containing t, v_x, and v_yand v (columns E-H).

Insert a chart into your spreadsheet.

	Choose the standard type XY (Scatter), and the sub-type Scatter. Give the chart the title "Velocity (t)" and label the axis. Choose to show a legend.
	Place the chart as an object in sheet 3.

Save your workbook.

Word

	Paste your "Velocity (t)" chart into your Word document.
	In a few sentences, interpret the plots in this chart.
	Save your Word document.

Excel

Find the x- and y-components and the magnitude of the acceleration as a function of time

Produce columns J-M in your spreadsheet (sheet 1) as shown below.

J	K	L	M
t(s)	a_x(m/s^2)	a_y(m/s^2)	a(m/s^2)
1.00	2.00	0.00	2.00
2.00	2.00	0.00	2.00
3.00	2.00	0.00	2.00
...	...	...	...

Column J contains the average time for the data points in the same row and the data points in the next row of columns E-H.

[In cell J2 type =(E2+E3)/2. Then right-click the cell and choose Copy. Highlight the remaining cells in column J, J3 to the next to last cell with an entry in column E. Right-click and choose, Paste.]

Column K contains the x-component of the acceleration, averaged over successive time intervals.

[In cell K2 type =(F3-F2)/(E3-E2). Then right-click the cell and choose Copy. Highlight the remaining cells in column K, K3 to the next to last cell with an entry in column E. Right-click and choose, Paste.]

Column L contains the y-component of the acceleration, averaged over successive time intervals.

Column M contains the magnitude of the velocity.

[In cell M2 type =SQRT(K2^2+L2^2). Then right-click the cell and choose Copy. Highlight the remaining cells in column M, M3 to the next to last cell with an entry in column E. Right-click and choose, Paste.]

Highlight all your newly created cells and click Format, Cells, Number, and choose 2 or 3 decimal places.

Plot the x- and y components and the magnitude of the acceleration of the object as a function of time.

Save your workbook.

Word

	Paste your "Acceleration (t)" and chart into your Word document.
	In a few sentences interpret the plots in this chart.
	Save your word document.

IExcel

Estimate the total distance traveled by the object. For each time interval we have approximately d = vDt. We sum the distances traveled in successive time intervals to find the total distance traveled.

Produce column O in your spreadsheet as shown below.

Distance (m)

...

[In cell O2 type +H2*(A3-A2). In call O3 type +O2+H3*(A4-A3). Right-click the cell O3 and cchoose Copy. Highlight the remaining cells in column O, O4 to the next to last cell with an entry in column A. Right-click and choose, Paste.]

On page 1 of your word document enter

Name:
E-mail address:

Laboratory 3 Report

and answer the following questions.

	What is its initial position of the car?
	What is its initial velocity (magnitude and direction)?
	Is its velocity constant during some time interval?
	Is its speed constant during some time interval?
	Are there time intervals during which it is accelerating?
	If it is accelerating, what is the direction of its acceleration?
	What is the shape of its trajectory?
	What is its displacement vector in the time interval for which you have data?
	What is the total distance traveled?

Save your Word document (your name_lab3.doc) and attach it to an e-mail message to mbreinig@utk.edu.

Solving physics problems with a spreadsheet

Objective

Information about the data set

Procedure

Excel

Word

Excel

Word

Excel

Word

Excel

Word

IExcel

Name: E-mail address:

Laboratory 3 Report

Name:
E-mail address: