Excel - Data Analysis - DUE TONIGHT, IN 4 HOURS
Page 1
CS 171G Lab 12 – Big Data
(Data Analysis , Part 2)
* There is NOT a Module to read for this lab.
What to Submit:
Submit the completed Excel file, CallLength.xlsx.
Install the Analysis Toolpak add-in for Excel. If you’re not familiar with installing add-ins, follow the steps below.
Click on the File tab, then click Options to open the Excel Options window.
Select Add-Ins on the left panel, Analysis ToolPak on the right panel, then click Go.
Page 2
In the Add-Ins pop-up window, click to put a check mark in the box next to Analysis ToolPak, then click OK.
Download the Excel file CallLength.xlsx from Canvas. In this workbook, each number is the
length of a call to a customer service center. They vary a great deal. Some calls last less than 10 seconds, while others last half an hour.
1. Open CallLength.xlsx file. Click on the Data tab, then click the Data Analysis button
(near the right side of the ribbon). In the list of Analysis Tools, select Descriptive
Statistics, then click OK. The Descriptive Statistics pop-up window will be displayed.
(Note: if you don't see the Data Analysis button, it means the Analysis Toolpak is not
installed. Return to the instructions on page 1 and install it.)
Page 3
2. Fill the text boxes and check boxes as shown below, then click OK.
3. A new worksheet named Summary stats will be created with the following table. (You may
need to adjust the widths of the columns so that all of the text will display.)
Page 4
4. In Sheet1 of CallLength, add the sequence 0,10,20,…,1000 to column C (from C2 to
C102).
5. Click the Data tab, then click the Data Analysis button. In the list of Analysis Tools,
select Histogram, then click OK. The Histogram pop-up window will be displayed.
Fill in the boxes and select options as shown below, then click OK.
Page 5
It may take several minutes for your computer to calculate all of the frequencies needed
for the histogram. Be patient. When the calculations are finished, you should see a new
worksheet tab labeled "histogram". Click on the worksheet tab. You should see a chart similar to this one:
6. Delete the legend (right-click it and choose delete). Change the chart title to "Customer
Service Calls Received". Add a horizontal axis title that says "Call length (in seconds)".
7. Using the Summary Stats and the Histogram, answer the following questions. (Enter your
answers in the histogram worksheet, just below the histogram chart.)
A. How many calls are shown in the data?
B. What is the average (mean) call length in minutes?
C. What is the length of a "typical" call? Use the histogram and give your answer as
between _______ minutes and __________ minutes.
D. If you were a call center employee and your manager asked you to explain the large
number of calls that lastest less than 10 seconds, what would you say?
8. Save the workbook.
Submit the completed Excel file, CallLength.xlsx.