Wednesday, May 11, 2011

Data Manipulation in Excel

Excel can be used for many things: quickly finding the mean for a set of data, calculating standard deviation and even......... making a cartoon of Mike Tyson. Today students completed a worksheet on their computers where they needed to manipulate sets of data. Below is the worksheet with the hyperlinks.

Homework: study for your test on Friday. The information on standard deviation will be on the final exam, but not on the test on Friday.

Using Excel to Manipulate Data

A. Open the first file: 2003 American League Baseball Salaries

1. List three ways in which you could manipulate this data.

2. Sort the data so that each position is grouped together. Make sure that the salary of each player stays with the correct position. How many catchers are there?

Find the mean salary for each of the positions and fill in the information in the table below.


1st Base




3rd base

2nd base

Which position has the highest mean salary?

B. Open the Price of Electricity file.

3. How many years have complete records for the price of electricity?

4. Create a scatter plot in Excel (or numbers) showing the average price in electricity for the years 1986-1996. Make sure to correctly label the x and y axis and write a title for the graph. Add your graph to this word document. Unsure how to make an xy scatter plot? Click through this PowerPoint if you are a Mac user.

5. In your own words, explain standard deviation in the space below. If you don’t know what standard deviation is, read about it here (it’s the first term on the list).

6. Go back to your Excel / Numbers file. Calculate the standard deviation for 1986-1995. To do this, click in the cell next to the annual average and type =STDEV(

Then highlight the data for each month in that specific year

Close the ) and press enter/return.

Highlight this cell and place the cursor on the edge of the cell so it changes from an arrow to a +

Drag the blue edge straight down the column, until you have reached 1995.

You now have the standard deviation for each year.

7. Add standard deviation bars to your graph. If you do not know how to do this, watch this video if you use a Mac or watch this video if you have the new version of Windows. You may only be able to add the error bars for three years at a time.

8. Turn your graph from an xy scatter plot into a bar graph. Again, make sure it is correctly labeled.

9. What does the green triangle in the left top corner of each standard deviation box signify?

10. Which year had the most consistent gas price?

11. Which year had the largest standard deviation? What does this tell you about the gas prices that year, compared to the other years?

12. On a scale of 1-10 (ten being impossible, one being super easy) how hard was this assignment? Please write the name of each person in the group and his/her response

13. Please complete the feedback table below. For each of the four tasks, please write if you already knew how do to this or what it is. Write yes if you knew it before or no if you didn’t not know it before class today.


What standard deviation is

How to find standard deviation

How to add error bars to a graph

How to find the mean using Excel

No comments: