Project 1

Data Summaries and Pivot Tables in Excel Due: Thursday, March 10th, before class

Download file project1.xls from Blackboard. Type in your name on every worksheet. Turn in the printed copies of completed spreadsheets “Qualitative”, “Quantitative”, and “Probabilities”at the beginning of the class on March 10th.

Assingments submitted within the first 2 hours after the deadline get a 5% penalty, and a 10% penalty for every additional 2 hours.

Part I. Qualitative Data

A. According to ABC Media Research, the five top TV shows were Congo, X-Files, Holiday, Ellen, and Unhappily. Data for a sample of 75 viewers are in the spreadsheet “Qualitative” ofproject1.xls. Fill in the values for the frequency distribution table. Use Excel functions.

Note: You can use function COUNTIF(array, criteria) to obtain category counts for the frequency distribution, where

- – array is the sample data (press F4 key to make it an absolute reference so that you can correctly copy the formula),
- – criteria are the names of all the TV shows in the data (select Congo in the frequency distribution tables as input). For frequencies of other shows, you copy this formula down to proper cells.

B. Based on frequency distribution, construct a bar chart and a pie chart.

Your bar chart has to meet the following requirements:

- (1) Use the first subtype of Column chart as chart type.
- (2) Use TV Shows as chart title.
- (3) Enter TV Show for Category (X) axis and Frequency for Value (Y) axis.
- (4) Turn off legend.
- (5) The gap width = 0. You may right-click on a bar and select “Format Data Series”. Then click
the Option tab and change the “Gap width” to 0.

- (6) Move and resize the chart to fit the range of C10:H25 in Worksheet “Qualitative”.

C. Your pie chart has to meet the following requirements:

- (1) Use the first subtype of Pie chart as chart type.
- (2) Use TV Shows as chart title.
- (3) Make sure your pie chart shows both Category name and Percentage.
- (4) Turn off legend.
- (5) Move and resize the chart to fit the range of C27:H42 in Worksheet “Qualitative”.

QMTH 205 Spring 2016

Part II. Quantitative Data

A. Worksheet “Quantitative” contains monthly salaries of a sample of 15 Business graduates (in the order from low to high). Enter formulas to compute summary measures and fill in the distribution table.

Useful functions:

average(range), median(range), var.s(range), stdev.s(range), max(range),min(range)

Do not use functions percentile(range) and quartile(range) since they use an algorithm different from that in your text. Instead, use the algorithms in your text: first enter a formula for index i (Positioning Point), and then find the quartile or percentile based on index i.

Note: Function =FREQUENCY(Data_array, Bins_array) for frequency distribution of salaries The Bins_array is given just below the heading of Bin. Select an output area with the same size and shape as Bins_array (C21:C25 for our assignment). Type in the formula. Hold Shift and Ctrl keys and press Enter key.

Use the procedure Histogram to get the frequency distribution and histogram.

- (1) Click Data tab, (Tools menu in older versions) and select Data Analysis command.
- (2) Select Histogram and click OK button.
- (3) Click the selection button for Input Range and select the sample data.
- (4) Click the selection button for Bin Range, select bins_array given in the distribution table
(B21:B25 in this assignment).

- (5) Check Chart Output box.
- (6) To check Output Range, click the selection button, select A29, and press Enter key. Click
OK button.

If Data Analysis is not there, go to File, Options, select Add Ins, and then Analysis ToolPak.

B. Given a sample with two variables, X and Y, compute the sample covariance and the correlation coefficient for X and Y. Use fnctions COVARIANCE.S(array, array) andCORREL(array, array).

Make a scatter diagram for variables X and Y. Your chart has to meet the following requirements:

- (1) Use the first subtype of Scatter chart as chart type.
- (2) Use Scatter Diagram as chart title.
- (3) Enter X for Value (X) axis and Y For Value (Y) axis.
- (4) Turn off legend.
- (5) Move and resize the chart to fit the range of D44:H56 in Worksheet “Quantitative”.

2

Part III. Pivot Table and Empirical Probability

Worksheet “Restaurant” contains the data on Quality Rating and Meal Price for a sample of 230 restaurants.

A. Create a contingency table for the variables Quality Rating and Meal Price in worksheet“Probabilities”. Use classes of $10-$19, $20-$29, …, $40-$49 for Meal Price (the row variable) and classes of Good, Very Good, and Excellent for Quality Rating (the column variable). Use a pivot table in Excel:

- (1) Go to Insert and choose PivotTable.
- (2) Select data range (3 columns of data, including column headings), select New Worksheet,
click OK.

- (3) Assign row and column variables. Drag the field you are counting (“Restaurant” in this
exercise) to “∑ Values” field.

- (4) To rearrange the categories of Quality Rating, right-click on a category and choose Move
to move it left or right.

- (5) Group Meal Price variable into classes: right-click on a value of Meal Price, select Group
option, and enter numbers for Starting At, Ending At, and By.

- (6) Get frequencies: click on the arrow under “Values”, choose Value Field Settings, and
select Count.

(7)Copy the contingency table to worksheet “Probability”. Make sure to Paste

Special/Values.

- Create a joint probability table. Hint: you will need only one formula copied across the entire table.
- Use formulas and the tables you created to answer the following questions:

- (1) Let A be the event that a restaurant serves meals ranging between $20 and $29 in price. Estimate the probability of event A.
- (2) Let B be the event that a restaurant has a Good Quality Rating. Estimate the probability of event B.
- (3) What is the probability that a restaurant serves meals ranging between $20 and $29 in price and a Good Quality Rating?
- (4) What is the probability of a restaurant serving meals ranging between $20 and $29 in price, or a Good Quality Rating, or both?
- (5) Given that a restaurant serves meals ranging between $20 and $29, what is the probability that this restaurant has a Quality Rating of Good?
- (6) Are events A and B independent?
- (7) What is the probability that a restaurant has an Excellent Quality Rating, or the Meal
Price between $30 and $39, or both?

- (8) Given that the meal prices in a restaurant range from $30 to $39, what is the probability
that this restaurant has a Very Good Quality Rating