1 INTRODUCTION In physics, you will often encounter linear systems. When analyzing linear equations, the slope generally contains useful physical information. Importantly, in many cases it may also be useful to know the uncertainty associated with the slope. A powerful tool within Excel helps us to obtain both. For statistical analysis of a large data array, visuals are often used to educate the reader as to what the analysis looks like for a large (repeated) set of measurements of a system. Excel contains a mechanism to analyze these systems as well. LAB OBJECTIVE The student in this lab will learn how to use LINEST within Excel to obtain a slope of a linear line and the uncertainty of the slope. Secondly, the student will learn how to obtain a histogram from a large data array. Finally, the student will be taught how to present the data in an organized and structured way so that the reader (in our case the grader) will be able to properly interpret what is being reported. Least-Square Fitting of Linear Graphs A free-fall experiment may be modeled as a particle in motion with constant acceleration. Namely, v=vt gt [Note the similarity to y = mx + b]. In the above equation, v is the velocity of the particle. The y- intercept is the velocity of the particle at time t = 0, which is referred to as vg. The slope of the line Excel File Edit View Insert Format Tools Data Window Help Autasave) GU Home Insert Draw Page Layout Formulas Data Review View Calibri (Body) V 12 V A A 三三 ab V ab a Wrap Text Paste B I V V < 三三三 5 Merge & Center A1 X & fx A B C D E F G H J 0 W NA 4 5 6 7 Introduction to Analysis in Physics Introduction to Analysis in Physics 2 is free-fall acceleration () near the Earth's surface. Note that for a certain instant of time, v is what's being measured When forming a hypothesis and testing it with a carefully performed experiment (carefully cali- brated instruments, proper measuring devices, etc.), applying the least-squares fitting method to the data is often utilized for validation of the hypothesis. Since we are concerned with linear equations, we will use our velocity vs. time relationship on the previous page to introduce the method of least squares. To begin, let us define the residual: d; = (v fir; – v) For this expression, the residual d, is the distance between the data point v, and the corresponding point on the line vfir at a time t, Sometimes this quantity is positive and sometimes negative. This can create annoyances. Therefore, it is customary to square this quantity. Thus, squaring the residual and summing over all data points we obtain {d} = {(v fii – v;)' = (vo+ gt;v)? Note here that the hypothesis for the best-fit line is a linear function. Namely , vfiri = vo + gt; In calculating the above equation, one is determining the values of g and vo, which will minimize the sum, and determine the quality of the fit. The smaller the squared residual, the better the fit. Activity Using Excel's LINEST Function LINEST is a function within Excel which will perform a least-square fitting routine. The function will provide uncertainties for the determined slopes and y-intercepts. In this exercise, you will be presented two sets of data. You will use this data set to perform a spreadsheet analysis. After the spreadsheet is prepared, think about how to interpret the data. Two “particles” are starting from rest at the same point. Once in motion, the motion of these par- ticles is in one direction only. The equations governing the motions of “particle 1” and “particle 2” are given below: V = voit at V2 = V02 + azt When the particles are released, the speed of each particle at the same point in time is given in the data tables. We want to plot the speed of each particle as a function of time according to the instructions below to obtain a, and az: 1. Open up Excel. Merge cells Al and Bi by highlighting them and click “Merge & Center on the toolbar. Label the merged cell as “particle 1." Do the same for D1 and El and label the merged cell as “particle 2." Label columns A2 and D2 as “time (s).” Label columns B2 and E2 as “speed (m/s).” Notice the parentheses in the labels. These are units of the quantities time and speed, which are “seconds” and “meters/second” respectively. > 2. Fill the data that follows into the appropriate columns starting in the third cell for each. Note something about these data. How many significant figures do the data have? Also, note that we are using a measuring device to collect particle data, and we will assume the measuring devices for each particle have the same precision. Our device collects data in one-second intervals. Introduction to Analysis in Physics 3 Particle 1 Particle 2 0.00 0.00 0.00 0.00 1.00 2.01 1.00 2.23 2.00 3.91 2.00 5.32 3.00 6.02 3.00 7.53 4.00 8.11 4.00 10.4 5 10.3 5 12.4 12.1 6 15.3 6 7 14.2 7 17.4 8 16.3 8 20.3 3. After the data is entered, shade a 2-column by 5-row matrix under the inputted data from A14 through B18. With the 2 x 5 matrix remaining shaded, click on the equation bar at the top and then click the fx function. 4. When a box appears, select a category. Chose STATISITICAL and then LINEST. 5. The following is the input (see appendix for LINEST output format): Known_y's: B3:B11 (You have just selected column B as your dependent variable.) b. Known_x's: A3:A11 (You have just selected column A as your independent variable. Note: time is always the independent variable) Const: TRUE (You have selected that the line is in the form of y= mx + b.) d. STATS: TRUE (You have instructed Excel to provide error estimates of the slope and inter- cept.) C. 6. Click OK. Make sure all of the cells are still highlighted. You should see a number in cell A14. It should be 2.0405 m/s2 (We included the units for good measure.). This is your slope, which here represents the acceleration of the particle. However, we are assuming our devices are good to only three significant digits. Thus, we express this value only as 2.04 m/s². 7. Follow this next step very closely (your 2 x 5 matrix should still be highlighted). Highlight the entire equation in the equation box including the “equal” sign. Simultaneously press the Ctrl/ Shift/Enter keys. More output should now be visible. The uncertainty of the slope is displayed in cell A15, and should be 0.011128. Cell B14 should be -0.05644. This is your y-intercept. Cell B15 should be the uncertainty in the y-intercept. Cell A16 your R", which should be close to 1.00 when a potentially good fit is achieved. is very Introduction to Analysis in Physics 4 8. Cell B16 is termed the variance in y. Cell A17 is termed the Fisher F-statistic, which is some- times used to check for the integrity of the model chosen to fit the data. Here, we chose the linear model. To understand why it may be important to examine the error analysis using further statistical meth- ods, let's look at the output for the uncertainty of the slope and intercept. Note: Though our R2 value looks very close to 1.00, the uncertainties are a better way to determine the quality of the fit. Therefore, when doing analysis, we will extract these uncertainties whenever possible. 9. Next you will determine the best fit line. Label C1 as “Fit 1." Click on the cell C3. In that cell, in the equation box, type = $A$14*A3+$B$14. Hit enter. Take a moment to understand this equation. (The dollar signs ($) surrounding the column heading denote that the value within that particular cell is a constant.) Now select cell C3 and click the small square in the bottom right corner of the cell and drag it down to C11. This will copy the formula from C3 to the rest of the C11. 10. Now create a graph of your data including the best fit line. Highlight cells from A3 to C11. This will highlight all of the data including the best fit values. You will want to go to the Insert tab on the tool bar and click the small box with the arrow in the chart section to open the Insert Chart dialog box. Click on All Charts and select XY Scatter. Make sure you pick the option which has two series (series 1 = measured data, series 2 = best fit values) and click OK. Your column A is the independent variable while columns B and C are dependent. top of the 11. Click on the points directly on the graph which represent the column C (the fit). Both column A and column C data should be highlighted. On the chart tools at the page, click Format. Then to the upper-left part of the sheet, click Format Selection. On the right of the sheet, click the paint bucket icon and then you should be able to make the best fit into a line graph. Choose a color that will allow you to see the measured data points. 12. To format your graph, click on the graph and use the options just to the side of it to add a title, adjust the width, add axis titles, and adjust scaling. Click on the chart and on the tool bar, then click design if you want to make your chart look more creative. 13. Finally, let's place error bars on our graph. Click on the measured data on the chart. At the top on the chart tools, click Design. On the upper left, click Add Chart Element. Select More Options. You should then be able to edit the error bars in the side toolbar. To edit the vertical error bars (Series Y) for direction click both. Click in the End Style and select No Cap and set the Error Amount inside the brackets to the Fixed Value 0.01. Error bars should appear on the graph. Next, click on Error Bar Options on the top of the side toolbar and select Series X, which defines horizontal error bars. Do the same operations. Note: You can change the values to see how it looks on the graph if you want. 14. Finally, click Insert/Textbox and place it under the graph. Inside the textbox, click Insert/ Equation. The slope of the velocity vs. time graph is called the “acceleration" of particle 1. This quantity is simply the rate of change of velocity. 15. Right click on the tab name and rename the tab as “particle 1." 16. Add a new tab and call it “particle 2." Repeat all of the steps to generate your own analysis. Introduction to Analysis in Physics 5 17. Add a third tab and call it "summary.” Based on the fact that we measured the velocity and time with a device, and using the results of your slope, give a summary of your analysis. A few examples of things to include in your summary are: notes about how the experiment was carried out, possible sources of error, and other observations you made. [Note: You are merely trying to establish good habits. Do your best to receive full credit.] 18. Now we want to generate a histogram. Histograms are very useful, especially if you have taken a large N-number of measurements and you want to determine the frequency ticular value occurs. for which a par- 19. Create a fourth tab called “histogram." 20. In cell A1, click the fx function. Look at all of the function commands and chose Randbetween. For the bottom number, chose 20 and for the top number chose 100. Press OK. You then want to "drag" the formula all the way down to A300. Essentially what you have done is generated (300 times) a random number between 20 and 100. 21. Important: right-click on the data and copy. Over that same data set, right-click and under Paste Options click on paste Values (this is paste icon with numbers on it). This will ensure the numbers will not keep changing as you perform your analysis. 22. Next, in cell C3 and D3, label them “bin” and “frequency respectively. In the “bin” column, starting in C4 through C12, type 20, 30, 40, ... 100. Cell 12 should have 100. 23. Next, starting with D4, highlight down to D12. With the cells highlighted, type in cell D4 frequency (A1:A300, C4:C12) and do NOT press enter. With the cells still highlighted, simultaneously hit Ctrl/Shift/Enter. Notice that this is an array. It will give you an error if this is not done properly. 24. Now you should see numbers from D4 through D12. What do these represent? ? 25. You are now ready to create your histogram. Highlight the cells C4 through D12 only. Go to the Insert tab on the toolbar and click the small box with the arrow in the chart section to open the Insert Chart dialog box. Click on All Charts and select Column Chart. Be sure to select the Column Chart that has only one series. 26. You should see both the bin and the frequency plotted. Click on one of the blue bars. In the chart tools, click Format/Format Selection and set the gap value to zero. In addition, format the chart by selecting the paint can icon in the toolbar and choosing solid line for the border, and select a visible color as the outliner. 27. This should now look like a histogram. 28. Save this file as “Intro to analysis.” 29. Upload the file to Canvas.
Purchase answer to see full attachment