Physics Labs Preliminary Worksheet
Part 1 : What Is A Spreadsheet? K13
time (s) angle (radians) count angle (radians)
FIGURE 1. Paste by value. FIGURE 2. Little box.
Part 2 : Spreadsheet Formulas
Average Angle height ( m)
Welcome to the Physics Lab class. We use spreadsheets in our labs, as the most convenient way to record and analyze the data from our experiments; it also allows for our worksheets to be graded by computer. Since most students haven’t had spreadsheet experience, this preliminary is a quick tutorial. Along the way, we’ll also cover a couple of key concepts we’ll be using this semester to analyze our experiments: how to calculate relative error, and how to keep track of accuracy with significant digits. Since some of you are familiar with spreadsheets already, we use this format: in each part, the first box describes what you need to do, and the second box explains step-by-step how to do it. (Even if you know all about spreadsheets, be sure to check the text in bold.)
HOW TO DO THAT: A spreadsheet displays data in a table. Down the left side, you should see numbers for each row, and along the top there are letters for each column. These are used to identify the location of each piece of data stored in the cells of the table. The red cell, for example, is at K13. To enter a number or some text into a cell, just click on the cell so that it is outlined, then double-click inside it so that the cursor shows up, allowing you to type into the cell. You can also copy-paste whole chunks of data at once: click-and-drag over all the cells you want to copy; right-click on your selection and select COPY from the menu; then right-click in the location you want to paste them, and select the option to paste “by value”, as seen to the right in Figure 1. All data pasted into the color-coded parts of our worksheets should be pasted by value, to ensure the computer can read your answers correctly. To get a first look at how useful spreadsheets can be, type the number 1 into cell O16, and 2 into cell O17. Then, instead of typing the rest, just select those two cells, then click on the box in the boundary on the lower right, as in Figure 2, then drag it down the column. It will increment the rest of the values for you.
WHAT TO DO: Enter the data from the column of angle measurements into the labeled and color-coded area at P16 – P25, or copy it over (by value). Cells O16 – O25 should be filled with successive integers, beginning with 1.
WHAT TO DO: Use spreadsheet formulas to calculate the average and standard deviation of the angle data from P16 – P25, and enter the results in the labeled cells. Fill in the table titled “Height” with calculated heights for each of the angles, where height = 4.86 sin (angle).
HOW TO DO THAT: Creating a spreadsheet formula begins with typing an equals sign ( = ) into any empty cell; use the area outside of the black borders for your calculations. One of the built-in formulas is AVERAGE; as it is typed in, the spreadsheet shows you a list of formulas beginning with those characters, and you can click on the one you want. Once you have =AVERAGE( , you need to tell the spreadsheet which cell or cells you want to use in your formula. In this case, you could type in P15:P26, or just click and drag over those cells. After that, you’ll need to put a closing parentheses ) and hit ENTER. A numerical answer should appear; if not, some mistake was made. Copy-paste the answer – by value! – into the cell labeled “Average Angle”, or just type it in. To find the standard deviation, use the =STDEV() formula on the same angle data. Don’t use formulas directly in color-coded answer cells, just values. We can build up complex formulas, using standard mathematical notation like +, – , * for multiplication, / for division, and ^ for exponent. Our height formula is relatively simple: starting with the first cell, we type in =4.8*SIN(P15) and hit ENTER. Now, you could type in formulas for each cell, using P16 in the next one where this one has P15, and so on. but it’s much easier to select your cell, click on the little box in the corner like before, and drag it down. If you look at the formulas in the cells just created, you’ll see that the row number goes up for each one – if you pulled the first cell sideways instead, it would be the column letters that changed for each one. This is the easy way to analyze a lot of data at the same time.
NEXT: At the bottom of the spreadsheet, you should be able to see several tabs, labeled “Worksheet”, “Precision”, and “Graphs”. These are the pages included in this worksheet; click on the tab to go to the page. Each page should be completed. Pages can be large, but each has a black border around it to mark its limits.
Physics Labs Preliminary Worksheet
Part 2: Error
Table 1 Relative Errors
expected value (m) measurement 1 (m) measurement 2 (m) biased percent error between expected value and measurement 1 (%) unbiased percent error between measurements 1 and 2 (%)
1.23 1.287 1.252
1.2345 1.2087 1.242
WHAT TO DO: Complete the table. Don’t assume that this is the same as in other classes, different conventions are used in different fields.
Physics Labs Preliminary Worksheet
Part 3: Precision
Table 2: Read the Worksheet Guide,Already
Should numerical answers include units? (yes / no)
The physics labs use the ____-number rounding convention.
The (significant digits / digits of precision) are the digits after the decimal point.
The (significant digits / digits of precision) are the digits between the first and last nonzero digits.
Calculated results from two measurements can only be as precise as the _____ precise measurement involved.
The number of digits in a sum of two measurements depends on their (significant digits / digits of precision).
The number of digits in a product of two measurements depends on their (significant digits / digits of precision).
The number of digits in the result of a trig function depends on the (significant digits / digits of precision) of its input.
Table 3: Significant Figures
Numerical Measurement or Result # of Significant Digits # of Digits of Precision
123.45 + 1.234
123.45 * 1.234
HOW TO DO THAT: The Worksheet Guide, available in the Syllabus section on Blackboard, explains in detail how significant figures and rounding are handled; read it. Don’t assume this is the same as in previous classes, as it is probably not. 25% of the points can be lost on each mathematical answer that has the wrong number of figures or is rounded incorrectly, so it’s important to learn how to do it right early. Table 2 presents a few fill-in-the-blank questions that are easily answered by anyone looking at the Worksheet Guide. Keep in mind the speeling must be korrekt for the computer to recognize it. If you are given a choice of answers in bold italic, use exactly one or the other. Table 3 asks you to determine the number of significant digits or digits of precision in a variety of measurements, and also of calculated results from those measurements. Note the table does not ask for your calculated results, just the number of digits; but it may sometimes be necessary to perform the calculation, to determine both the significant digits and the digits of precision of the result.
WHAT TO DO: Complete the two tables below.
Physics Labs Preliminary Worksheet
Part 4: Graph
time (seconds) distance (meters)
HOW TO DO THAT: First, select the data you’ll make a graph from: click on cell C7, and then drag the cursor down to cell D19 before releasing the button, so that the selected data is highlighted. Then, click on INSERT in the menu at the top of the spreadsheet program. You will be shown a variety of things that can be inserted; from the Charts section, select the Scatter plots, and choose the one without lines, as seen in Figure 3 below. FIGURE 3. Scatterplot chart highlighted. Figure 4. The three buttons. A graph of your data will appear. The data in the column on the left will be used as the horizontal axis of the graph, the column on the right as the vertical axis. Before using it in a lab report, though, it needs a label, along with its axes, including their units of measurement, so readers can know exactly what the graph is showing them. To relabel the graph, click on “Chart Title” and replace the text with a new label. (Make up whatever you like. In general, you’d want one that would allow anyone looking at the graph to immediately understand what it is about.) After that, notice that when you click on the graph, three buttons appear to the side (as seen in Figure 4 above.) Click on the plus sign, and select “Axis Titles” from the menu. Once the axis titles appear, you can click on each of them and replace them with appropriate labels. Be sure to always include the units. Curve-fitting: Now that we have a graph, we can use the spreadsheet to do some graphic analysis. In general, when we engage in analysis of experimental results in physics, we have some mathematical model that gives us a theoretical prediction of how our data should behave, and we are comparing our actual experimental data to that model. To add a model to your graph, click on the graph, and then on the “plus” button that appears, on the triangle to the right of “Trendlines” in the menu that then appears, and finally on “Linear” in the last menu. This makes a line appear on our graph, but we also want to know the equation of this line, and how closely our data fits our model. So, go back to the last Trendlines menu and select “More Options…”. A section appears at the right of the screen with the trendline options; at the bottom is “Display Equation on chart” and “Display R-squared value on chart”; select both of those. The equation should appear on your graph. If it covers up any data, move it out of the way. The equation is given in the standard linear form y = mx + b, where m is the slope of the line, b is the y-intercept, x is the variable on the horizontal axis and y the variable on the vertical axis. The fitted line is the equation of that form that comes closest to matching all of the data. R2 is a statistic of the portion of the variation in the data explained by our model; at R2 = 0, it does not match at all; at R2 = 1, the model explains the data completely.
WHAT TO DO: Make a scatterplot graph from the data given below, labelling it and its axes, and put it in the colored area below. Fit a linear model to the graph, displaying the model equation and R2 value.