EXCEL PROJECT - DESCRIPTIVE STATISTICS

We will learn how to create a frequency distribution in Excel using frequency counter and other formulas. Using the data for #44 on page 52, create an extended frequency distribution. This has columns which include: class, frequency, midpoint, relative frequency, and cumulative frequency. We will also learn how to create graphs in Excel. These include a frequency histogram, frequency polygon, relative frequency histogram, and ogive.

Select File, Save As... and save the spreadsheet to your home directory.

In C3, type the word “Maximum”.

In D3, type “=max(A1:A51)”. The A1:A51 selects every value in the data set. The formula =max returns the maximum value of the data set.

In C4, type the word “Minimum”.

In D4, type “=min(A1:A51)”. The formula =min returns the minimum value of the data set.

In C5, type the word “Range”.

In D5, type “=D3-D4” This tells us the difference between the highest and lowest value (the range).

In C6, type the word “Classes”.

In D6, type the number “8” as the problem asks for eight classes.

In C7, type the word “Class Width”. To find the class width, we need to take the range and divide it by the number of classes. This value is then rounded up to the next convenient number.

In D7, type “=ROUNDUP(D5/D6,0)”. The D5/D6 finds the class width and the =roundup function automatically rounds up to the number of decimal places specified in the function. In our case, this is zero.

We now have all of the information we need to create the expanded frequency distribution. The expanded frequency distribution has columns for class, frequency, midpoint, relative frequency, and cumulative frequency.

In cell F4, type “=F3+D7” since D7 is the class width. Continue down until you have eight classes listed. (In F5, type “=F4+D7”. In F6, type “=F5+D7”….until in F10, type “=F9+D7”)

In cell G3, type “=F4-1”. This keeps the classes from overlapping.

In cell G4, type “=G3+D7” since D7 is the class width. Continue down until you have eight classes listed. (In G5, type “=G4+D7”….until in G10, type “=G9+D7”)

Select all cells that will be included under the column frequency (H3 to H10). Type “=frequency(A2:A51,G3:G10)” and then ctrl+shift+enter. This fills in all of the frequency values.

We want to check to make sure all values were counted correctly. To do this, type “=SUM(H3:H10)” into cell H11. Since we have 50 data values, this function should return the number 50.

In cell I3, type “=average(F3,G3)”. This will give the midpoint of the class. If you then select I3 to I10 and type ctrl+D, the midpoints will be entered for each row.

In cell J3, type “=H3/$H$11”. This divides the class frequency by the total frequency which gives the relative frequency. Select J3 to J10 and type ctrl+D. The $ sign with H11 keeps that the same in each cell.

We want to check to make sure the sums of the relative frequencies equal one. To do this, type “=SUM(J3:J10)” into cell J11.

In cell K3, type “=H3” since the first value in our frequency and cumulative frequency are the same.

In cell K4, type “=K3+H4”. This provides us with the cumulative frequency for the second row. Select cells K4 to K10 and hit ctrl+D. All remaining cumulative frequencies are now completed.

OUR EXPANDED FREQUENCY TABLE IS DONE! Please save the file.

CREATING CHARTSFrequency Histogram:

Select all values in the frequency column except for the total sum.

Go to the insert tab, the column drop-down, and select a 2-D column graph. The graph should appear on your screen.

The bottom labels are wrong. For a frequency histogram we want midpoints. The Chart Tools tab should be open at the top. If so, choose select data from the top bar and then Edit for the horizontal axis. A pop-up for axis labels should appear. The range should be the midpoint values (=Sheet1!I3:I10).

Consecutive bars should touch on the frequency histogram. Double-click on one of the bars in the histogram. The format data series box will pop-up. Slide the gap width selector to no gap.

Finally, we want to label the axes and the chart. The current chart look does not allow this, so we must change the chart layout. Under the Chart Tools: Design tab at the top, there is a chart layout section. We want the eighth layout which includes a chart title, titles for each axis, and no legend. The chart title should be Frequency Histogram. The vertical axis is frequency. The horizontal axis is Number of hospitals in each state.

You may change the colors and text style on the chart.

Frequency Polygon:

Select the frequency column. Go to the insert tab, the line chart drop-down, and select the 2-D line with markers. The graph should appear on your screen.

The bottom labels are wrong. For a frequency polygon we want midpoints. The Chart Tools tab should be open at the top. If so, choose select data from the top bar and then Edit for the horizontal axis. A pop-up for axis labels should appear. The range should be the midpoint values (=Sheet1!I3:I10).

We want to label the axes and the chart. The current chart look does not allow this, so we must change the chart layout. Under the Chart Tools: Design tab at the top, there is a chart layout section. We want the tenth layout which includes a chart title, titles for each axis, and a legend. Because we do not want the legend, delete it from the chart. The chart title should be Frequency Polygon. The vertical axis is frequency. The horizontal axis is Number of hospitals in each state.

You may change the colors and text style on the chart.

Relative Frequency Histogram:

The steps are the same as for the frequency histogram, but choose relative frequency instead.

Select all values in the relative frequency column except for the total sum.

Go to the insert tab, the column drop-down, and select a 2-D column graph. The graph should appear on your screen.

The bottom labels are wrong. For a frequency histogram we want midpoints. The Chart Tools tab should be open at the top. If so, choose select data from the top bar and then Edit for the horizontal axis. A pop-up for axis labels should appear. The range should be the midpoint values (=Sheet1!I3:I10).

Consecutive bars should touch on the frequency histogram. Double-click on one of the bars in the histogram. The format data series box will pop-up. Slide the gap width selector to no gap.

We want to label the axes and the chart. The current chart look does not allow this, so we must change the chart layout. Under the Chart Tools: Design tab at the top, there is a chart layout section. We want the eighth layout which includes a chart title, titles for each axis, and no legend. The chart title should be Frequency Histogram. The vertical axis is Relative frequency. The horizontal axis is Number of hospitals in each state.

You may change the colors and text style on the chart.

Ogive:

Select the cumulative frequency column. Go to the insert tab, the line chart drop-down, and select the 2-D line with markers. The graph should appear on your screen.

The bottom labels are wrong. For an ogive we want upper class limits. The Chart Tools tab should be open at the top. If so, choose select data from the top bar and then Edit for the horizontal axis. A pop-up for axis labels should appear. The range should be the upper limits (=Sheet1!G3:G10).

We want to label the axes and the chart. The current chart look does not allow this, so we must change the chart layout. Under the Chart Tools: Design tab at the top, there is a chart layout section. We want the tenth layout which includes a chart title, titles for each axis, and a legend. Because we do not want the legend, delete it from the chart. The chart title should be Ogive. The vertical axis is cumulative frequency. The horizontal axis is Number of hospitals in each state.

You may change the colors and text style on the chart.

PLEASE SAVE THE FILE!!!

Homework Submission

Please fill-out this form when your excel file has been completed. Attach the excel file and submit for grading.