2.10 Univariate descriptive statistics using spreadsheets to view  (Page 4/5)

Optional classroom exercise:

At your computer, try this exercise: (1) Open the file, Statistics First Day of Class Survey that you worked on previously (2) open the file in Excel; (3) create a new worksheet tab and label it BoxPlot for Quantitative Date; (4) pick a column of data that is quantitative and has been “cleaned” and create a BoxPlot with appropriate titles, axis labels, near and far outliers; (5) save the file again and post in the appropriate Moodle assignment.

Creating Stem and Leaf Graphs

Using the same process that we used for creating the dot plot we will replace the “dot” with the leaf for each of the numbers in our column of quantitative data. For this example I used the data on How many credit hours have you completed to date. The data list included: 124, 84, 72, 95, 165, 100, 72, 95, 26, 110, and 0. For the stem we are using the tens place value. For the leaf we will use the unit place value. You will create two columns labeling the first column Stem and the second column Leaf. In the Stem column you will enter the numbers 0 – 12 or 1 – 11. This is the range of “tens” in your data set. For the Leaf column you will enter the repeat function and the countif function. You will ask Excel to repeat the “unit value” for as many times as the countif function found the unit value you specified. So for this example if I wanted to find the Leaf for the 7 “tens” column I would be entering =rept(“0” countif(beginning cell address):(end cell address), the cell address of the Stem,*10+0)&=rept(“1” countif(beginning cell address):(end cell address), the cell address of the Stem,*10+1)&=rept(“2” countif(beginning cell address):(end cell address), the cell address of the Stem,*10+2). . . . =rept(“9” countif(beginning cell address):(end cell address), the cell address of the Stem,*10+9). The results will look like the excel spreadsheet below. Google Spreadsheet will also look the same. We have just used this opportunity to display the formula in the Google Spreadsheet for you. Right clicking on the Stem row you can select format cell, then select the border tab, and then select the style and color for your line clicking on only the right border of the cells. You have created a stem and leaf graph. If you use “absolute” notation as the example does once you have entered the first line of your leaf you can copy and paste the rest into cells below. It goes quit quickly.

Optional classroom exercise:

At your computer, try this exercise: (1) Open the file, Statistics First Day of Class Survey that you worked on previously (2) open the file in Excel; (3) create a new worksheet tab and label it Stem and Leaf for Quantitative Date; (4) pick column of data that is quantitative and has been “cleaned” and create a Stem and Leaf with appropriate titles, labels, and lines; (5) save the file again and post in the appropriate Moodle assignment.

Creating summary statistics for quantitative data

For quantitative data there are measures of center and spread that we will want to evaluate in order to determine (1) whether data is nearly normal, skewed to the right or skewed to the left or (2) whether we should display data with a histogram or on a boxplot. Below are the formulas for calculating center and spread using Google Spreadsheet or Excel.