OBJECTIVES:

· Calculate Descriptive statistics for quantitative data in Excel

· Sort data in Excel to calculate descriptive statistics for subsets of a population

· Construct side-by-side boxplots to compare subsets of a population

· Compare the statistics and interpret the boxplots to reach conclusions about the population

In this activity we will be learning how to use Excel to create numerical and graphical summaries for a data set.

Stat 1400Descriptive Statistics Activity

2

Descriptive Statistics on Excel

In our last activity we analyzed the demographics of the workforce for a small manufacturing company. This week we will continue to analyze the salaries of the employees of this company.

To calculate the statistical summary for the salaries, click on the Data menu at the top of the screen

Select Data Analysis at the far right (if that function is not listed you will need to add it on—see instructions at the beginning lab activity 1). Choose Descriptive Statistics from the drop-down menu.

Click on the box for Input Range and highlight the salaries of all employees. Click on the box before Summary statistics then click on OK. The descriptive statistics will be formatted into a table that will display on a new page of the worksheet.

Change the name on the table to Salaries – all employees. Click on the line between column A and column B and drag it to the right to make column A wide enough for the names of the statistics. Copy and paste the table below:

Click on the Sheet1 tab at the bottom of the screen. Now click on column B (gender) to highlight it. Then click on on the right side of the ribbon menu at the top of the screen when you are in the home screen and select Sort A to Z. Click on ‘Expand the selection’ then click on Sort. This will sort the rows listing the female records first followed by the male records.

Click on Data on the ribbon menu at the top of the screen then click on Data Analysis at the far right. Choose Descriptive Statistics from the drop-down menu.

Click on the box for Input Range and highlight the salaries of all the female employees. Click on the box before Summary statistics then click on OK. The descriptive statistics will be formatted into a table that will display on a new page of the worksheet. Make the first column wider and change the name of the table to ‘Female Salaries’. Copy and paste the table here:

Calculate the descriptive statistics for the male salaries following the same steps, but this time highlight all the male salaries for the input range. Edit the table in the same way as before. Copy and paste the table here:

Use the tables to answer the following questions:

1. What is the typical salary for an employee of this company? Remember the measures of center (mean or median) give the typical value of a data variable.

2. What is the relationship between the mean and the median of the salaries of all employees? (Circle answer)

MEAN > MEDIANMEAN = MEDIANMEAN < MEDIAN

What does this tell us about the shape of the histogram for the salaries of all employees? (You made this histogram in lab activity 1.)

3. Now compare the mean and median of the female employees to the mean and median of the male employees. Who earns more money at this company – males or females? Justify your choice using the statistics you compared.

4. Now compare the standard deviation of the female employees to the standard deviation of the male employees. Whose salaries are more consistent – males or females? Justify your choice using the statistics you compared.

Boxplot

Type ‘Female Salaries’ in cell G1. Highlight the female salaries. Select Copy from the ribbon menu at the top of the screen. Paste the female salaries in column G starting at cell 2.

Type ‘Male Salaries’ in cell H1. Highlight the male salaries. Select Copy from the ribbon menu at the top of the screen. Paste the male salaries in column H starting at cell 2.

Now highlight the female salaries and male salaries in columns G and H including the names of the columns. Click Insert on the menu at the top of the screen. Click on the All Charts tab and select Box and Whisker. Click on OK. Give the graph an appropriate title.

Copy and paste the graph below.

Use the graph to answer the following questions.

5. How do the boxplots show the relationship between the medians you compared in question 3?

6. How do the boxplots show the relationship between the standard deviations you compared in question 4?

7. What else do the boxplots reveal that the descriptive statistics did not reveal?