Unformatted text preview: Data Analysis in Excel 2007 Michael Assis Making a plot 1. Add your data to two vertical columns, first x‐values, then y‐values 1 2 3 4 5 8 2. Select the data cells 3. Click on the Insert menu 4. Click on Scatter 5. Click on the first option 6. Under Chart Layouts, click on the first option 7. Click on Chart Title and Axis Title to change titles and axis labels (and add units) 8. Right‐click on the x‐axis, and select Add Major Gridlines 9. Delete the key for the data points (it says “Series1”), optional Example
9 8 7 6 5 4 3 2 1 0 0 0.5 1 1.5 2 2.5 3 3.5 Velocity (m/s) Length (m) Adding a Best Fit Line 1. Make a plot as above 2. Right‐click on a data point, and select Add Trendline 3. Select Set Intercept in order to force the best fit line to pass through the origin (or other point) 4. Select Display Equation on Chart Example
9 8 7 6 5 4 3 2 1 0 0 0.5 1 1.5 2 2.5 Velocity (m/s) y = 2.7143x 3 3.5 Length (m) Adding another set of data to a plot 1. Make a plot, as above 2. Right‐click inside the plot, and select Select Data 3. Above the table to the left, select Add 4. Click on the Series X Values field, then use your mouse to select cells for the x‐axis 5. Click on the Series Y Values field, then use your mouse to select cells for the y‐axis Example
9 8 7 6 5 4 3 2 1 0 0 0.5 1 1.5 2 2.5 Velocity (m/s) y = 2.7143x 3 3.5 Length (m) Using Excel to calculate formulas 1. Make a column of your x‐axis values 1 2 3 4 5 2. In the first cell in the next column, type “=” followed by a formula, clicking on the cell to the left whenever you need its value. Example for : type “=”, click on the cell with the “1”, then type “^2”. If the cell with “1” is in location A1, the formula will look like “=A1^2”. 1 2 3 4 5 =A1^2 Press Enter 3. Click on the cell with the formula. Notice the black square at the bottom right of the cell. Click and drag it down till the cell next to the cell with “5”. Let go of the mouse. 1 2 3 4 5 1 4 9 16 25 4. Click on the cell with a “9”. Notice that it changed A1 in the formula to be A3 automatically. 5. In order to prevent Excel from automatically changing a particular value, use the “$” symbol. Example for 5 : Click on cell C1 and enter the value 5. Click on cell B1 and now type “=$C$1*A1^2”. Use step 3 to add this formula to the other cells in the B column. 1 2 3 4 5 5 20 45 80 125 5 6. Now click on cell B3. Notice that the $C$1 remains unchanged. 7. Click on C1 and change its value to 7 8. Notice the automatic change in values in the columns 1 2 3 4 5 7 28 63 112 175 7 Using Excel functions Example for using the function Average 1. Continuing from above, click on an empty cell, B6 for example. 2. Type “=average(“ 3. Use your mouse to select cells B1 through B5 4. Type “)” to close the function, and press Enter 1 2 3 4 5 7 28 63 112 175 77 7 Useful Excel functions Count Sum Average Max Min ...
View
Full Document
 Fall '01
 Rijssenbeek
 Physics, Click, (m/s), best fit line, Add Major Gridlines, Select Set Intercept

Click to edit the document details