Reference no: EM133794413 , Length: word count:800

1. You are a statistician contracted to sample and analyze weights of the 5 lb bags of coffee. You collect the following data:

Compute the following coffee bag statistics using individual Excel functions (not Data Analysis, Descriptive Statistics):

2. There are several ways to calculate quartiles that result in different answers. QUARTILE and QUARTILE.INC can produce a result that is not in the dataset or an unexpected result if you have a dataset with an even number of values.

3. Maximum
Minimum
Range

4. For the standard deviation, when do you use STDEV.S and when do you use STDEV.P?

5. The unit of physical measure associated with the standard deviation is the same as unit of data.
When calculating variance, the unit is squared. When data is in inches, variance is in inches squared.
Would you scale graphs and histograms in increments of variance or standard deviation?

Explain why the standard deviation is more useful than the variance in scaling data.

7. As a contracted statistician, you have no prior knowledge of the company's coffee product,
and the company has no prior statistics with which to assess the current data. Explain
to the manufacture why there is or is not a concern with average bag weight comparing
your mean or median statistics to the nominal bag weight of 5 lbs.

8. What are two situations where the CV is especially useful (Week 2 Presentation slide 13)?

9. Again, explain to the manufacturer why there is or is not a concern with the variability of the product
using the appropriate coffee bag weight statistic.

See the tab "Used Car Data". This list represents a consolidated list from 2021 of used cars for sale on Craigs List in the Athens, GA area. The data set contains 421 used cars from the year 1995 or newer that sold for $1000 or more. The list also includes columns like price, condition, manufacturer and other relevant categories a buyer might find of interest.

Create a pivot table on a new worksheet showing the frequency and percent frequency of the manufacturer from the Used Car Data.
Click anywhere in the data table.
Tab Insert > PivotTable > OK.
Drag manufacturer to Rows and Values. Drag manufacturer to Values again.
Right click on "Count of manufacturer2", Show Values As, % of Grand Total".

10. What is the most common used-car manufacturer?

What are the two least common?

11. Create a two way pivot table on a new worksheet showing Year vs. Condition using the Used Car Data.
Click anywhere in the data table.
Insert > PivotTable > OK.
Drag year to Rows and condition to Columns and to Σ Values.

12. What does the pivot table of condition vs. year tell you?

13 Frequency Distribution of the number of caramel popcorn cans sold by 70 scout troops in Maryland.

14 The number of bins in a frequency table depends on the data. Determning the number of bins begins with a rule of thumb
such as square root or cube root of sample size, which are based on advanced statistical theory.
The next step is to vary bin width to find the best width.
Too few bins flattens any pattern in the data and too many breaks up any pattern in the data.
We will use 5 bins. Now find the bin width by dividing the Range by 5, then round up the result to the nearest whole number.
You can use Excel function CEILING to round up. Decrease decimals in the Number group will not round up the calculation.
Width of each bin is
15 Find the frequency distribution using Excel array function FREQUENCY.

16 Create a histogram with Data Analysis, Histogram. Use bin upper limits in #15 for the Bin Range input.
The bin "More" produced by Data Analysis can be deleted if its frequency is 0.
17 As part of the marketing group of a film company, you are asked to find out the age distribution of the audience of the latest film.
From 500 responses, you find that 50 are younger than 6 years old, 86 are 6 to 9 years,
165 are 10 to 14, 25 are 15 to 21, and 174 are older than 21.

a) Make a frequency table of these categorical data.

b) Make a relative frequency table.

c) Make a bar chart using counts in the frequency table.

d) Would the distribution of responses in a bar chart of relative frequencies be different from the distribution of responses in a bar chart of frequencies?
e) Make a pie chart.

18 Write a few sentences describing the distribution across age groups shown by your charts.

19 The audiences interviewed were also asked if they had seen the movie before (Never, Once, More than Once):

a) Compute the marginal frequency distribution by Viewing Frequency in a new column labeled "Total".

b) Compute the marginal frequency distribution by Age Group in a new row labeled "Total".

c) What percentage of all audience members are over 21?

d) In the Viewing Frequency group "More Than Once", what percentage are Over 21?

20 a) Compute the relative frequency distribution for each Age Group in order to compare distributions by Age Group.
The frequency distributions in #19 cannot be compared directly because the Age Group sizes are unequal.
For each Age Group, divide count by column subtotal (not the grand total). If you do this correctly, the first column will have 84%, 10%, and 6%.
b) Make a stacked bar chart of the relative viewing frequencies by Age Group in 20 a):
Select all the cells within the red-dashed rectange above, Insert, Insert Column or Bar Chart, and 2D Column and Stacked.

c) Examine the stacked bar chart of the distribution by Age Groups.
If the relative frequency distributions are similar or the same, then Age Group is no more than a label.
If the distributions are different, then Age Group matters because it determines distribution.
Are Age Groups similar or same, or are they different and Age Group matters?