{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

excel_data_analysis - Data Analysis in Excel 2007 Michael...

Info icon This preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

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

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern