The purpose of this laboratory exercise is to get familiar with electronic spreadsheets by using them in some simple applications.
Equipment:
Computer with Microsoft Excel software.
Procedure:
1. Your instructor will give you a brief explanation of how a spreadsheet works and show you some of the basic operations and functions.
2. Open the Microsoft Excel program.
3. Create a simple spreadsheet that calculates the following function: f(x) = A sin(Bx + C). Your experimental values are A = 5, B = 3, and C = π/3. Place these values at the right side of the spreadsheet in the region reserved for constants. Put the words amplitude, frequency, and phase next to each as an explanation for the meaning of each constant. Place column headings for "x" and "f(x)" near the middle of the spreadsheet, enter a zero in the cell below "x", and enter the formula shown above in the cell below "f(x)". Be sure to put an equal sign in front of the formula. Create a column for values of x that run from zero to 10 radians in steps of 0.1 radians. Use the copy feature to create these x values. Similarly, create in the next column the corresponding values of f(x) by copying the formula shown above down through the same number of rows.
*Author's note: Use the dollar ($) sign before the letter and number designation of the cell that you want the value to remain constant. This is because the copy feature will include the empty cells beneath your constant values (e.g. A = 5, B = 3, C = π/3). The resulting values under the "f(x)" column will not be the values you need.
4. Once the generated data looks reasonable, copy this data onto a clipboard by highlighting the contents of the two columns and choosing EDIT => COPY from the menu bar. Print out a copy of your spreadsheet (the first 20 rows or so) and also print out the spreadsheet formulas (press CTRL ~).
*Note that in the "x" column, the formulas are: =[previous cell] + 0.1. The formulas in the "f(x) column are: =$E$3*SIN($F$3*[corresponding "x" column cell]+$G$3). The dollar signs ($) before the letter and number designation of the cells E3, F3, and G3 mean that the values of cells E3, F3, and G3 are constant throughout the copying process. The only value that changes is that of the cells in the "x" column.
5. Minimize the spreadsheet window and run the Graphical Analysis program by opening the Physics Apps icon and double-click the Graphical Analysis icon. Once the program loads, click on the top of the "x" column then choose EDIT => PASTE to place the data from the clipboard into your graphing program. A graph of the data should appear in the graph window. Put appropriate labels on the horizontal and vertical axes of the graph.
6. Highlight the portion of the graph you want to analyze and choose ANALYZE => CURVE FIT from the menu bar to direct the computer to find a function that best fits the data. From the list of possible functions, give the computer a hint as to what type of function you expect your data to match. The computer should display a value for A, B, and C that fit the sine curve that you are plotting. Make a copy of the data and graph by selecting FILE => PRINT.
7. Repeat the above process for a spreadsheet that calculates the position of a freely falling particle as a function of time. This time, your constants should include the acceleration of gravity (g = 9.8 m/s2), the initial position (x0 = 1000
m), initial velocity (v0 = 50
m/s), and the time increment (Δt = 0.2 s). Print out the spreadsheet (Resulting values and formulas). Again, copy the data into the Graphical Analysis program and obtain a graph of position vs time. Fit this data to a function (y = A + Bx + Cx2) which closely matches the data. Get the print out of this graph with the data table.
Conclusion:
In this lab exercise, I've learned how to properly write certain formulas into Excel. I did not know the functionality of the dollar sign ($) before this exercise. I've also learned how to properly calculate and transfer data from Excel to the Graphical Analysis program. This exercise is not limited to linear or quadratic formulas. You can create a complex formula and Excel will calculate the data with the given parameters and limitations.
