Morgan Extra Pages Graphing with Excel (revised 6/10/13) 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. 1 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 vertical plus-drags 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. 2 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. 3 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 4 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 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. 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- 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 5 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′) 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 yo. 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. Note the parentheses around the intercept term of Equation 1′ to emphasize that the minus sign is part of it. 8. Present your values of k and yo with their units neatly at the bottom of your spreadsheet. Equating above and below, you can create two useful new equations: 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? slope m = k (Eq. 2) y-intercept b = -kyo (Eq. 3) 10. Do the Homework, Further Exercises on Interpreting Linear Graphs. 6
Purchase answer to see full attachment