[Back to Home]
Chapter 3 Technology Assignment: p. 115 parts a, b & c.
Use Excel to calculate the mean, median, mode, range, variance, standard deviation, and quartiles for a sample of quantitative data.
PART A:
There are two ways of doing some of these calculations. If you cannot find the Data Analysis dialogue box, the follow the second set of instructions. Those of you who do have the Data Analysis dialogue box may want to do the exercise twice so that you are aware of both methods.
---- First Method ----
Step 1: Open the file located in Hints on Blackboard (it contains the data so that you don't have to enter all the numbers) or open an excel file and enter the set of data located on p. 115.
Step 2: Save the file in your documents folder.
Step 3: Order the data from smallest to greatest by first highlighting the column and then clicking the AZ button. (In the newer version of Excel you will find this under the Data tab.) When done you will be easily able to read off the smallest and largest values of the data set.
Step 4:Click on a cell in another column, then if you have the older version of Excel go to Tools > Data Analysis or if you have the newer version of Excel go to the Data tab > select Data Analysis and then select Descriptive Statistics from the list.
Step 5:This is the new dialog box that will open:
For the input range select the entire set of data.
For the output range select a blank area of your spreadsheet.
Be sure to check off the box that says "summary statistics".
Click OK.
Step 6: You will now see a two column chart that has various statistics listed. Highlight (either bold, change font color or cell background color) the ones that are asked for on page 115 part a.
---- Second Method ----
Step 1: Open the file located in Hints on Blackboard (it contains the data so that you don't have to enter all the numbers) or open an excel file and enter the set of data located on p. 115.
Step 2: Save the file in your documents folder.
Step 3: Order the data from smallest to greatest by first highlighting the column and then clicking the AZ button. (In the newer version of Excel you will find this under the Data tab.) When done you will be easily able to read off the smallest and largest values of the data set.
Step 4: Use the following formulas to find the values requested. In each case type the formula and then in the parentheses be sure to select the entire list of data. Each parentheses should look something like this (A2:A47), it may vary depending in which cells the data are contained.
For Mean: =average( )
For Median: =median( )
For Mode: =mode( )
For Range: =max - min (you may select the cell that has the max and the cell that has the min, ie. =A47-A2)
For Variance: =var( )
For Standard Deviation: =stdev( )
Step 5: Label each value accordingly using a cell either to the left or the right or above each calculation. (You may also experiment with changing the font or cell color.)
PART B:
Step 1: For a box-and-whisker plot you will need the five number summary. These values include the minimum, quartile 1, median, quartile 3 and maximum values. There is only one formula you need to use:
=QUARTILE(array,quart) .... it should look something like this for the minimum value: 
For minimum the "quart" value is 0
For first quartile the "quart" value is 1
For median the "quart" value is 2
For third quartile the "quart" value is 3
For maximum the "quart" value is 4
Step 2: Label each value accordingly using a cell either to the left or the right or above each calculation.
Step 3: Unfortunately there is no way to create a box-and-whisker plot with Excel. You may be creative and do it any way you want to. If you draw it by hand, please turn in during class.
PART C:
Step 1: You will now repeat parts a and b with a modified data set. You will complete the same calculations but without the extreme data values of 100, 105, 113 & 138).
Step 2: Answer the following questions somewhere on your spreadsheet:
i) Which of the three averages is most affected by eliminating the four extreme data values? (Mean, median or mode.)
ii) What happened to the standard deviation when these four values were not included in the calculation?
iii) How do the two box-and-whisker plots compare?
Final Step: Submit your work.
Save the file to have a file name that includes the assignment number, your last name, and your section according to this format: Chapter3_LastName_01(if you are in the 1:30 PM class) or (_02 if you're 3:00 PM class). For example: Chapter3_Smith_01 or Chapter3_Jones_02, etc.
Send this file as an attachment to my BUBBS email: angela.j.loudon@biola.edu
If you have any questions, email Angela Loudon on BUBBS.