Morgan Extra Pages Graphing with Excel to be carried out in a computer lab, 3rd floor Calloway Hall or elsewhere Name Box Figure 1. Parts of an Excel spreadsheet. The Excel spreadsheet consists of vertical columns and horizontal rows; a column and row intersect at a cell. A cell can contain data for use in calculations of all sorts. The Name Box shows the currently selected cell (Fig. 1). In the Excel 2007 and 2010 versions the drop-down menus familiar in most software screens have been replaced by tabs with horizontally-arranged command buttons of various categories (Fig. 2) Figure 2. Tabs. ___________________________________________________________________ Open Excel, click on the Microsoft circle, upper left, and Save As your surname.xlsx on the desktop. Before leaving the lab e-mail the file to yourself and/or save to a flash drive. Also e-mail it to your instructor. EXERCISE 1: BASIC OPERATIONS Click Save often as you work. 1. Type the heading “Edge Length” in Cell A1 and double click the crack between the A and B column heading for automatic widening of column A. Similarly, write headings for columns B and C and enter numbers in Cells A2 and A3 as in Fig. 3. Highlight Cells A2 and A3 by dragging the cursor (chunky plus-shape) over the two of them and letting go. 2. Note that there are three types of cursor crosses: chunky for selecting, barbed for moving entries or blocks of entries from cell to cell, and tiny (appearing only at the little square in the lower-right corner of a cell). Obtain a tiny arrow for Cell A3 and perform a plus-drag down Column A until the cells are filled up to 40 (in Cell A8). Note that the two highlighted cells set both the starting value of the fill and the intervals. Figure 4. A formula. 5. Highlight Cells B2 and C2; plus-drag down to Row 8 (Fig. 5). Do the numbers look correct? Click on some cells in the newly filled area and notice how Excel steps the row designations as it moves down the column (it can do it for horizontal plusdrags along rows also). This is the major programming development that has led to the popularity of spreadsheets. Figure 3. Entries. 3. Click on Cell B2 and enter a formula for face area of a cube as follows: type =, click on Cell A2, type ^2, and press Enter (note the formula bar in Fig. 4). 4. Enter the formula for cube volume in Cell C2 (same procedure, but “=, click on A2, ^3, Enter”). Figure 5. Plus-dragging formulas. Figure 6. Creating a scatter graph. 6. Now let’s graph the Face Area versus Edge Length: select Cells A1 through B8, choose the Insert tab, and click the Scatter drop-down menu and select “Scatter with only Markers” (Fig. 6). 7. Move the graph (Excel calls it a “chart”) that appears up alongside your number table and dress it up as follows: a. Note that some Chart Layouts have appeared above. Click Layout 1 and alter each title to read Face Area for the vertical axis, Edge Length for the horizontal and Face Area vs. Edge Length for the Graph Title. b. Activate the Excel Least squares routine, called “fitting a trendline” in the program: right click any of the data markers and click Add Trendline. Choose Power and also check “Display equation on chart” and “Display R-squared value on chart.” Fig. 7 shows what the graph will look like at this point. c. The titles are explicit, so the legend is unnecessary. Click on it and press the delete button to remove it. Figure 7. A graph with a fitted curve. 8. Now let’s overlay the Volume vs. Edge Length curve onto the same graph (optional for 203L/205L): Make a copy of your graph by clicking on the outer white area, clicking ctrl-c (or right click, copy), and pasting the copy somewhere else (ctrl-v). If you wish, delete the trendline as in Fig. 8. a. Right click on the outer white space, choose Select Data and click the Add button. b. You can type in the cell ranges by hand in the dialog box that comes up, but it is easier to click the red, white, and blue button on the right of each space and highlight what you want to go in. Click the red, white, and blue of the bar that has appeared, and you will bounce back to the Add dialog box. Use the Edge Length column for the x’s and Volume for the y’s. c. Right-click on any volume data point and choose Format Data Series. Clicking Secondary Axis will place its scale on the right of the graph as in Fig. 8. d. Dress up your graph with two axis titles (Layout-Labels-Axis Titles), etc. Figure 8. Adding a second curve and y-axis to the graph EXERCISE 2: INTERPRETING A LINEAR GRAPH Introduction: Many experiments are repeated a number of times with one of the parameters involved varied from run to run. Often the goal is to measure the rate of change of a dependent variable, rather than a particular value. If the dependent variable can be expressed as a linear function of the independent parameter, then the slope and yintercept of an appropriate graph will give the rate of change and a particular value, respectively. An example of such an experiment in PHYS.203L/205L is the first part of Lab 20, in which weights are added to the bottom of a suspended spring (Figure 9). ing weights in newtons of 0.49, 0.98, etc. The weight pan was used as the pointer for reading y and had a mass of 50 g, so yo could not be directly measured. For convenient graphing Equation 1 can be rewritten: -(Mg) = - ky + kyo Or (Mg) = ky - kyo (Eq. 1′) Procedure 1. On your spreadsheet note the tabs at the bottom left and double-click Sheet1. Type in “Basics,” and then click the Sheet2 tab to bring up a fresh worksheet. Change the sheet name to “Linear Fit” and fill in data as in this table. Hooke’s Law Experiment y (m) -Fs = Mg (N) 0.337 0.49 0.388 0.98 0.446 1.47 0.498 1.96 0.550 2.45 Figure 9. A spring with a weight stretching it This experiment shows that a spring exerts a force Fs proportional to the distance stretched y = (y-yo), a relationship known as Hooke’s Law: Fs = - k(y – yo) (Eq. 1) where k is called the Hooke’s Law constant. The minus sign shows that the spring opposes any push or pull on it. In Lab 20 Fs is equal to (- Mg) and y is given by the reading on a meter stick. Masses were added to the bottom of the spring in 50-g increments giv- 2. Highlight the cells with the numbers, and graph (Mg) versus y as in Steps 6 and 7 of the Basics section. Your Trendline this time will be Linear of course. If you are having trouble remembering what’s versus what, "y" looks like "v", so what comes before the "v" of "versus" goes on the y (vertical) axis. Yes, this graph is confusing: the horizontal (“x”) axis is distance y, and the “y” axis is something else. 3. Click on the Equation/R2 box on the graph and highlight just the slope, that is, only the number that comes before the “x.” Copy it (control-c is a fast way to do it) and paste it (control-v) into an empty cell. Do likewise for the intercept (including the minus sign). SAVE YOUR FILE! 5. The next steps use the standard procedure for obtaining information from linear data. Write the general equation for a straight line immediately below a hand-written copy of Equation 1′ then circle matching items: (Mg) = k y + (- k yo) y = mx+ b (Eq. 1′) Note the parentheses around the intercept term of Equation 1′ to emphasize that the minus sign is part of it. Equating above and below, you can create two useful new equations: slope m = k (Eq. 2) y-intercept b = -kyo (Eq. 3) 6. Solve Equation 2 for k, that is, rewrite left to right. Then substitute the value for slope m from your graph, and you have an experimental value for the Hooke’s Law constant k. Next solve Equation 3 for yo, substitute the value for intercept b from your graph and the value of k that you just found, and calculate y o. 7. Examine your linear graph for clues to finding the units of the slope and the yintercept. Use these units to find the units of k and yo. 8. Present your values of k and yo with their units neatly at the bottom of your spreadsheet. 9. R2 in Excel, like r in our lab manual and Corr. in the LoggerPro software, is a measure of how well the calculated line matches the data points. 1.00 would indicate a perfect match. State how good a match you think was made in this case? 10. Do the Homework, Further Exercises on Interpreting Linear Graphs, on the following pages. Morgan Extra Pages Homework: Graph Interpretation Exercises EXAMPLE WITH COMPLETE SOLUTION In PHYS.203L and 205L we do Lab 9 Newton’s Second Law on Atwood’s Machine using a photogate sensor (Fig. 1). The Atwood’s apparatus can slow the rate of fall enough to be measured even with primitive timing devices. In our experiment LoggerPro software automatically collects and analyzes the data giving reliable measurements of g, the acceleration of gravity. The equation governing motion for Atwood’s Machine can be written:  g   f  a    m    M   M  Eq.1 where a is the acceleration of the masses and string, g is the acceleration of gravity, M is the total mass at both ends of the string, m is the difference between the masses, and f is the frictional force at the hub of the pulley wheel. In this exercise you are given a graph of a vs. m obtained in this experiment with the values of M and the slope and intercept (Fig. 2). The goal is to extract values for acceleration of gravity g and frictional force f from this information. To analyze the graph we write y = mx + b, the general equation for a straight line, directly under Equation 1 and match up the various parameters: Figure 1. The Atwood’s Machine setup (from the LoggerPro handout).  g   f  a    m    M   M  y m x  b Atwood's Machine M = 0.400 kg a = 24.4 m - 0.018 2  g  slope, m    M  ( Eq. 2) 0.010 0.020 0.030 0.040 0.050 0.060  m (kg) Figure 2. Graph of acceleration versus mass difference; data from a Physics I experiment. and  f  b   M  2 Equating above and below, you can create two new equations: a (m/s ) R = 0.998 1.40 1.20 1.00 0.80 0.60 0.40 0.20 0.00 0.000 (Eq. 3) To handle Equation 2 it pays to consider what the units of the slope are. A slope is “the rise over the run,“ so its units must be the units of the vertical axis divided by those of the horizontal axis. In this case: Exp.  Acc.  100 Acc. 9.76  9.80   100 9.80  0. 4% % Error  m / s2 m  kg kg  s 2 Now let’s solve Equation 2 for g and substitute the values of total mass M and of the slope m from the graph: g  Mm  0.400kg  24.4m /( kg  s 2 ) A similar process with Equation 3 leads to a value for f, the frictional force at the hub of the pulley wheel. Note that the units of intercept b are simply whatever the vertical axis units are, m/s2 in this case. Solving Equation 3 for f:  9.76 m / s 2 f   Mb  0.400 kg  ( 0.018 m / s 2 ) Using 9.80 m/s2 as the Baltimore accepted value for g, we can calculate the percent error:  7.2  10 3 kg m / s 2  7.2 mN EXERCISE 1 Picket Fence Drop v (m/s) The Picket Fence experiment makes use of LoggerPro software to calculate velocities at regular time intervals as the striped plate passes through the photogate (Fig. 3). The theoretical equation is v = vi + at (Eq. 4) 12 10 8 6 4 2 0 y = 9.8224x + 0.0007 R2 = 0.9997 0 where vi = 0 (the fence is dropped from rest) and a = g. a. Write Equation 4 with y = mx + b under it and circle matching factors as in the Example. b. What is the experimental value of the acceleration of gravity? What is its percent error from the accepted value for Baltimore, 9.80 m/s2? c. Does the value of the y-intercept make sense? d. How well did the straight Trendline match the data? 0.2 0.4 0.6 0.8 1 1.2 t (s) Figure 3. Graph of speed versus time as calculated by LoggerPro as a picket fence falls freely through a photogate. EXERCISE 2 V = IR (Eq. 5) Ohm's Law Potential difference,  V (V) This is an electrical example from PHYS.204L/206L, potential difference, V, versus current, I (Fig. 4). The theoretical equation is 0.4 y = 0.628x - 0.0275 2 R = 0.9933 0.3 0.2 0.1 0 0 and is known as “Ohm’s Law.” The unit symbols stand for volts, V, and Amperes, A. The factor R stands for resistance and is measured in units of ohms, symbol  (capital omega). The definition of the ohm is: V 0.1 0.2 0.3 0.4 0.5 0.6 Current, I (A) Figure 4. Graph of potential difference versus current; data from a Physics II experiment. The theoretical equation, V = IR, is known as “Ohm’s Law.” (Eq. 6) By coincidence the letter symbols for potential (a quantity ) and volts (its unit) are identical. Thus “voltage” has become the laboratory slang name for potential. a. Rearrange the Ohm’s Law equation to match y = mx + b.. b. What is the experimental resistance? c. Comment on the experimental intercept: is its value reasonable? EXERCISE 3 a. Rearrange Equation 5 solved for I to match y = mx + b. b. What is the experimental potential difference? c. Calculate the percent difference from the 15.0 V that the experimenter set on the power supply (the instrument used for such experiments). d. Comment on the experimental intercept: is its value reasonable? I (milliamperes) Current versus (1/Resistance) This graph (Fig. 5) also follows Ohm’s Law, but solved for current I. For this graph the experimenter held potential difference V constant at 15.0V and measured the current for resistances of 100, 50, 40, and 30  Solve Ohm’s Law for I and you will see that 1/R is the logical variable to use on the x axis. For units, someone once jokingly referred to a “reciprocal ohm” as a “mho,” and the name stuck. 600 500 400 300 200 100 0 y = 14.727x - 0.2214 R2 = 0.9938 5 10 15 20 R -1 25 30 35 (millimhos) Figure 5. Another application of Ohm’s Law: a graph of current versus the inverse of resistance, from a different electric circuit experiment. EXERCISE 4 The Atwood’s Machine experiment (see the solved example above) can be done in another way: keep mass difference m the same and vary the total mass M (Fig. 6). Atwood's Machine m = 0.020 kg f = 7.2 mN y = 0.1964x - 0.0735 2 R = 0.995 1.000 2 a (m/s ) a. Rewrite Equation 1 and factor out (1/M). b. Equate the coefficient of (1/M) with the experimental slope and solve for acceleration of gravity g. c. Substitute the values for slope, mass difference, and frictional force and calculate the experimental of g. d. Derive the units of the slope and show that the units of g come out as they should. e. Is the value of the experimental intercept reasonable? 0.800 0.600 0.400 2.000 2.500 3.000 3.500 4.000 4.500 5.000 1/M (1/kg) Figure 6. Graph of acceleration versus the reciprocal of total mass; data from a another Physics I experiment. EXERCISE 5 Effect of Pendulum Length on Period In the previous two exercises the reciprocal of a variable was used to make the graph come out linear. In this one the trick will be to use the square root of a variable (Fig. 7). T  2 L g ( Eq . 7 ) where the period T is the time per cycle, L is the length of the string, and g is the acceleration of gravity. a. Rewrite Equation 7 with the square root of L factored out and placed at the end. b. Equate the coefficient of √L with the experimental slope and solve for acceleration of gravity g. 2.000 T (s) In PHYS.203L and 205L Lab 19 The Pendulum the theoretical equation is 2.400 y = 2.0523x - 0.0331 R2 = 0.999 1.600 1.200 0.800 0.400 0.00 0.10 0.20 0.30 0.40 0.50 0.60 0.70 0.80 0.90 1.00 1.10 L 1/2 (m1/2 ) Figure 7. Graph of period T versus the square root of pendulum length; data from a Physics I experiment. c. Substitute the value for slope and calculate the experimental of g. d. Derive the units of the slope and show that the units of g come out as they should. e. Is the value of the experimental intercept reasonable? EXERCISE 6 In Exercise 5 another approach would have been to square both sides of Equation 7 and plot T2 versus L. Lab 20 directs us to use that alternative. It involves another case of periodic or harmonic motion with a similar, but more complicated, equation for the period: ( Eq . 8 ) where T is the period of the bobbing (Fig. 8), M is the suspended mass, ms is the mass of the spring, k is a measure of stiffness called the spring constant, and C is a dimensionless factor showing how much of the spring mass is effectively bobbing. a. Square both sides of Equation 8 and rearrange it to match y = mx + b. b. Write y = mx + b under your rearranged equation and circle matching factors as in the Example. c. Write two new equations analogous to Equations 2 and 3 in the Example. Use the first of the two for calculating k and the second for finding C from the data of Fig. 9. d. A theoretical analysis has shown that for most springs C = 1/3. Find the percent error from that value. e. Derive the units of the slope and intercept; show that the units of k come out as N/m and that C is dimensionless. Figure 8. In Lab 20 mass M is suspended from a spring which is set to bobbing up and down, a good approximation to simple harmonic motion (SHM), described by Equation 8. Lab 20: SHM of a Spring Mass of the spring, m s = 25.1 g 2 M  Cm s k T2 T  2 1.0000 0.8000 0.6000 0.4000 0.2000 0.0000 y = 3.0185x + 0.0197 R 2 = 0.9965 0 0.05 0.1 0.15 0.2 0.25 0.3 M ( k g) Figure 9. Graph of the square of the period T2 versus suspended mass M data from a Physics I experiment. EXERCISE 7 This last exercise deals with an exponential equation, and the trick is to take the logarithm of both sides. In PHYS.204L/206L we do Lab 33 The RC Time Constant with theoretical equation: V  Vo e t  (Eq. 9) where V is the potential difference at time t across a circuit element called a capacitor (the  is dropped for simplicity), Vo is V at t = 0 (try it), and  (tau) is a characteristic of the circuit called the time constant. 2.50 y = -9.17E-03x + 2.00E+00 2.00 lnV (lnV) a. Take the natural log of both sides and apply the addition rule for logarithms of a product on the right-hand side. b. Noting that the graph (Fig. 10) plots lnV versus t, arrange your equation in y = mx + b order, write y = mx + b under it, and circle the parts as in the Example. c. Write two new equations analogous to Equations 2 and 3 in the Example. Use the first of the two for calculating  and the second for finding lnVo and then Vo. d. Note that the units of lnV are the natural log of volts, lnV. As usual derive the units of the slope and interecept and use them to obtain the units of your experimental V and t. Discharge of a Capacitor 2 R = 9.98E-01 1.50 1.00 0.50 0.00 0 50 100 150 t (s) Figure 10. Graph of a logarithm versus time; data from Lab 33, a Physics II experiment. 200
Purchase answer to see full attachment