Compute Measures of Variability
Excel Homework 3
(70 pts possible)
The objective of your third Excel assignment is to learn to describe a data set using measures of central tendency and variability. First, be sure you view the presentation that covers computing central tendency and variability in Excel found in the Reading & Study folder in Module/Week 3. This presentation goes through the steps you will need to be familiar with in order to complete this assignment.
In Module/Week 3, the goal is to use Excel formulas to calculate specific measures of central tendency and variability of a given data set, using the steps you learned during the presentation. Open “Data Set 3,” found in the Assignment Instructions folder, under “Excel Homework 3,” then follow the steps below to complete Module/Week 3’s assignment.
1. Research Question: In Module/Week 3, the data comes from an internet survey that assessed the frequency of use of the social networking site Facebook ™. A psychologist interested in time spent visiting a social networking site collected data from 366 respondents concerning: 1.) number of visits to FB per day; 2.) number of times participants changed FB “status” per week; and 3.) participant age. She is interested in summarizing the data set using measures of central tendency and variability, and displaying these in a table.
2. In the Excel file, each participant’s responses are recorded in columns A through C, rows 2–367. As you scroll down through the data, you may notice that some values are missing. This is intentional and often happens when participants respond to certain items but not others on a survey.
3. You will also see a table to the right of the data which contains labels but no numbers. It is THIS table that you will be completing during this module/week’s assignment. An important note: It is essential to leave data values as they are during this module/week’s assignment—do not change any numbers. Your assignment grade is based on both the numerical values themselves as well as the formulas you use to derive them. Changing a number in the data set will affect the results of any calculations using the formulas (even if only slightly). If you find that you have changed a data value and forgotten it, you can open the original file from the Data Set document, compare values, and make any necessary changes to your own file.
4. Format Table Cells
Begin by formatting the blank cells within the table as the category “Number” with 2 decimal places. (See the presentation from the Reading & Study folder in Module/Week 1 on formatting cells if you need a refresher.) This will give your table a uniform look. (10 pts)
5. Compute Measures of Central Tendency
a. Using the “AVERAGE” function, display the means of all three variables in the appropriate cells of the table. (10 pts)
b. Using the “MEDIAN” function, display the medians of all three variables in the appropriate cells of the table. (10 pts)
c. Using the “MODE” function, display the modes of all three variables in the appropriate cells of the table. (10 pts)
6. Compute Measures of Variability
a. Using the formula that combines the “MIN” and “MAX” functions (as shown in this module/week’s presentation), display the range of all three variables in the appropriate cells of the table. (10 pts)
b. Using the “VARIANCE” function, display the variance of all three variables in the appropriate cells of the table. (10 pts)
c. Using the “STDEV” function, display the standard deviation of all three variables in the appropriate cells of the table. (10 pts)
You can test whether your formulas are properly linked by changing a value in one of the data columns—the numbers in the corresponding table cells should change (though usually only very slightly due to the large number of scores). However, remember to re-enter the original number in the data column as your grade is based on the data set as it was when you opened it in the Data Set document.
Save the completed file to your computer as “yourname_Excel3.xls”. Your finished Excel Worksheet should be submitted by 11:59 p.m. (ET) on Monday of Module/Week 3.
Page 1 of 2