statistic excel

chapter2_outline1.pptx

Home >Computer Science homework help >statistic excel

IV. Schedule
Date	Topic (Changes in chapters covered will be announced in class or on Bb)
Th., 1/30	Exam 1 (chapters 1, 2, and 3)
Th., 2/27	Exam 2 (chapters 6, 7, and 8)
Th., 4/3	Exam 3 (chapters 4, 13, 9, and 10)
Th., 4/24 (5:30-8:00)	Final Exam (comprehensive)

MAN 201

BUSINESS STATISTICS

Chapter 1

Review Question

Review Question Chapter 1:

Why use a sample– Why not measure the entire population rather than rely on a sample?

When we use a sample to draw conclusion about the entire population what branch of business statistics are we talking about?

What are the two type of samples?

In systematic sampling every kth member of the population is chosen for the sample, How do you calculate the value k?

What type of sample is a internet poll?

Review Questions Chapter 1:

An inspector needs to examine a shipment to determine the extent of deterioration of the parts in the shipment. The shipment consists of 2,000 sealed cartons, each containing 5 parts. If a simple random sample of 100 parts is to be examined, it is conceivable that as many as 100 cartons may have to be unsealed. The inspector wishes to open fewer cartons. Their sampling procedure calls for the selection of 20 of the 2,000 cartons at random and inspection of all parts in each selected carton.

What is the population size (N) ?

What is the entity?

What is the sample size?

What is the random sampling technique being used?

Review Questions Chapter 1:

A state teachers’ association is studying the educational qualifications of its membership, which consist of 50,000 elementary school, high school, and college teachers. A major characteristic of interest is the mean number of years of education of its members. The analyst used a sample design in which independent simple random samples of teachers are selected from among the member teachers in each of the three school levels.

What is the entity?

What is the sampling technique being used?

Review Questions Chapter 1:

What are the two main data types?

What are the data types of the following variables?

Occupational status

Saving account balance

Number of classes a student has this semester

What the difference between a sample and a census?

MAN 201

BUSINESS STATISTICS

Chapter 2

Organizing and Graphing Data

2-8

Recall the types of data from Chapter 1:

Displaying Quantitative Data

Quantitative

Qualitative

Types of Data

Discrete

Continuous

Ordinal

Nominal

Discrete vs. Continuous Data

Discrete data are values based on observations that can be counted and are typically represented by whole numbers

represent something that has been counted

take on whole numbers such as 0, 1, 2, 3

Continuous data are values that can take on any real numbers, including numbers that contain decimal points

usually measured rather than counted

Examples are weight, time, and distance

Nominal Scale vs. Ordinal Scale

Nominal scale is one that simply lists names as potential categories. As an example, the variable type of industry produces nominal responses such as:

construction

banking

retail

Ordinal scale involves a ranking or ordering of the categories. Various cuts of beef have such label as prime, choice, good, and so on, that imply difference in order.

Usually qualitative data are measured on nominal or ordinal scales.

Organizing and Graphing Data

The main purpose of this chapter is to present methods for organizing and displaying qualitative and quantitative data in tables and graphs.

Result- we hope to achieve clarity and extract information that might not have been apparent about the sample in its raw, unprocessed form.

Constructing a Frequency Distribution

A frequency distribution shows the number of data observations that fall into specific intervals

Graphically summarize information not readily observable by merely looking at data in a table

Constructing a Frequency Distribution

Example: Number of iPads sold per day

Ungrouped frequency distribution: A table consisting of two columns of information

Values of the variable

Frequency with which each value occurs in the data set.

Relative Frequency Distributions

Relative frequency distributions display the proportion of observations of each class relative to the total number of observations

shows the fraction of observations in each class

found by dividing each frequency by the total number of observations

the fractions in a relative frequency distribution add up to 1.00

Relative Frequency Distributions

Two iPads were sold on 28% of the days

Example:

Cumulative Relative Frequency Distributions

A cumulative relative frequency distribution totals the proportion of observations that are less than or equal to the class at which you are looking

Shows the accumulated proportion as values vary from low to high

Example:

Three iPads or less were sold on 80% of the business days

Pr(Number Sold Per Day <= 3) = 80%

Cumulative Relative Frequency Distributions

Using a Histogram to Graph a Frequency Distribution

A histogram is a graph showing the number of observations in each class of a frequency distribution

The Shape of Histograms

Symmetric

the right side is the mirror image of the left side of the distribution

Still symmetric, but wider spread

Not symmetric

Grouped frequency distribution: A table consisting of two columns of information – the values of the variable organized into classes and the frequency of the values that occur within each class.

Constructing a Frequency Distribution Using Grouped Quantitative Data

Ideally, the number of classes in a frequency distribution should be between 4 and 10

Some data sets, particularly those with continuous data, require several values to be grouped together in a single class

This grouping prevents having too many classes in the frequency distribution, which can make it difficult to detect patterns

Constructing a Frequency Distribution Using Grouped Quantitative Data

Example: 50 Dell Customer Support Hold Times (minutes)

Number of Classes

One method to determine the number of classes in a frequency distribution is the rule

2k > n

where k = Number of classes

n = Number of data points

Find the lowest value of k that satisfies the rule

Suppose n = 50

25 = 32 < 50 (k = 5 is too small)

26 = 64 > 50 (k = 6 is a good choice)

Class Width

Once k is known, the width of each class can be found

The width is the range of numbers to put into each class

There is no one correct answer for the class width.

The goal is to create a histogram to clearly and usefully show the pattern in the data

Often there is more than one acceptable way to accomplish this

Class Width

Class Width = (17.4 – 0.6) = 2.8 or 3

Class Boundaries

Class boundaries represent the minimum and maximum values for each class

Choose class boundaries that are easy to read

0 to less than 3 minutes

3 to less than 6 minutes

6 to less than 9 minutes

9 to less than 12 minutes

12 to less than 15 minutes

15 to less than 18 minutes

Class Frequencies

Find class frequencies by counting and recording the number of observations in each class

this is easier when the data are sorted

Example:

Creating Group Frequency Distribution Review

Solution:

Sort the data in ascending order

Observe the size of your data set (n=?)

Determine the number of classes 2k > n

Find the class width

Create the class boundaries based on your class width

Tally the number of data points within each class to come up with your frequency distribution.

Calculate relative frequency distribution and cumulative relative frequency distribution

Rules for Classes for Grouped Data

Equal-size classes. All classes in the frequency distribution must be of equal width

Mutually exclusive classes. Class boundaries cannot overlap (Principle of Exclusion)

Include all data values. Make sure all data values are accounted for in the total row of the frequency distribution (Principle of Inclusion)

Avoid empty classes. It is undesirable for a histogram to display a class so narrow that there are no observations in it

Avoid open-ended classes (if possible). These violate the first rule of equal class sizes

A stem and leaf display splits the data values into stems (the larger place values) and leaves (the smaller place value)

By listing all of the leaves to the right of each stem, we can graphically describe how the data are distributed

All the original data points are visible on the display

Easy to construct by hand

Provides a histogram-like view of the distribution

Stem and Leaf Display

Sort the data from lowest to highest

Determine the unique stem values

7, 8, 9 are the different stem values in this example

List the stems in a vertical column and then add the leaf values to the right of the appropriate stem, in ascending order

7 | 8 8 9 9 9

8 | 0 0 0 0 1 1 2 3 3 4 4 4 5 6 7 8

9 | 0 2 5

Stem and Leaf Display

To get more detail the stems can be split in half

7(5) | 8 8 9 9 9

8(0) | 0 0 0 0 1 1 2 3 3 4 4 4

8(5) | 5 6 7 8

9(0) | 0 2

9(5) | 5

The stem labeled 7(5) stores all the scores between 75 and 79

The stem 8(0) stores all the scores between 80 and 84

Pie Charts

Pie charts are another excellent tool for comparing proportions for categorical data

Each segment of the pie represents the relative frequency of one category

All categories in the data set must be included in the pie

Use a pie chart to compare the relative sizes of all possible categories

2-33

Chapter 2 Homework Assignment

2-34

2-35

2-36

2-37

2-38

2-39

70.5 - 76.5 76.5 - 82.5 82.5 - 88.5 88.5 - 94.5 94.5 - 100.5 0.2 0.26666666666666672 0.36666666666666675 0.13333333333333336 3.333333333333334E-2

Computer Homework Assignment #1

EXCEL Tutorial and Examples

Statistical Analysis Using Excel 2010

With Excel 2010 open, click on the File tab found in the upper left corner of your computer screen

Click Options shown in the drop down menu. This will open the Excel Options dialog box

Select Add-Ins in the left margin…

Click on Go at the bottom of the screen

Select the check boxes for Analysis ToolPak and Analysis ToolPak - VBA in the popup menu and click OK

Statistical Analysis Using Excel 2010

Select the Data tab. Click on Data Analysis on the right side of the application bar

The Data Analysis pop-up menu should appear in the spreadsheet

Constructing a Histogram in Excel

Select the Data tab, and click on Data Analysis in the upper right corner

In the pop-up menu, select Histogram and click OK…

2-48

Constructing a Histogram in Excel

In the Input Range text box, highlight the desired data

In the Bin Range text box, highlight the bin values (create bins if not already created before step 1)

For Output options, select New Worksheet Ply and Chart Output

Click OK

2-49

Additional formatting issues:

Use a descriptive title for the graph

Use descriptive labels for the axes

Remove the redundant “Frequency” legend

Remove gaps between bars

Constructing a Histogram with Grouped Quantitative Data

The Consequences of Too Few or Too Many Classes

Wide classes results in few class intervals

Can obscure important patterns

Gives a “blocky” distribution graph

Summarizes the data too much

Tells us little about the true distribution shape

Too many narrow classes in a histogram also has consequences

Results in a “jagged” histogram

Some classes may be empty

Does not summarize the data enough

2-52

Qualitative data are values that are categorical

Can be nominal or ordinal measurement level

Describe a characteristic, such as gender or level of education

Frequency distributions help display qualitative data by indicating the number of occurrences of various categories

Can use Excel’s COUNTIF function to count the number of values matching a category label

Displaying Qualitative Data

2-53

Excel’s COUNTIF Function

Bar Charts

Bar charts are a good tool for displaying qualitative data that have been organized in categories

Can be arranged in a vertical or horizontal orientation

2-54

Bar Charts

Horizontal bar chart Vertical bar chart

2-55

Can display multiple series with clustered bar charts or stacked bar charts:

Pie Charts

2-56

Constructing a Pie Chart in Excel

Pie Charts

2-57

Constructing a Pie Chart in Excel

(continued)

2-58

Class Assignment:

Read chapter 3 pages 91-115, 120-126, and 131-141

Work problems: 3.6, 3.8, 3.11,3.20, 3.35, 3.40, 3.50, 3.64, 3.66, 3.68.

value

data

Minimum

value

data

Maximum

width

class

Estimated

Min

Max

width

class

MGMT 201

Computer Homework #1

Note: Computer homework assignments are individual assignments and must be done

without assistance from or collaboration with other members of the class.

Collaboration on Computer Homework assignments will result in zero credit for the

assignment.

The purpose of this assignment is to help you get started using Excel to perform some of the

basic statistical functions we’ve talked about in class . The data set is 100 responses to a survey

given last year to MGMT 201 students . The questions in the survey are shown below:

1. Your age converted to months (e.g., 20 years and 3 months would be 243 months):

2. Distance in miles from where you lived during high school to UofL:

3. Your GPA:

4. Your major or planned major:

5. Your class year at UofL (F=freshman, S=sophomore, J=junior, R=senior, G=grad, O=other):

6. Have you taken statistics previously, either in high school or college? (yes or no)

7. Your level of anxiety about this course on a 1-5 scale with 1 = none and 5 = very high:

8. Average number of hours per week you work (for pay):

Number of other classes currently enrolled in: Indicate number (indicate 0, 1, 2 etc.)

10. The average number of hours per week you study during a normal semester.

The spreadsheet with the survey results is posted on Blackboard for this assignment. The data

values for the 100 students are arranged in columns in the spreadsheet. The requirements of this

assignment are shown below. We will us e this data set for at least one other computer homework

assignment.

1. The first thing you will have to do create descriptive statistics for the all quantitative

variables.

a. To do this, click on the Data tab at the top of the spreadsheet . The far right section

of the menu bar should be labeled “Analysis” and should include an item titled

“Data Analysis.” If Data Analysis does not appear, click on the File tab at the far

left of the tab bar, then click on Options (near the bottom), then clic k on Add-Ins

(next to the bottom of the list at the left of the window), then look at the bottom of

the window and you’ll see “Manage:” next to a drop down box. Make sure “Excel

Add-ins” appears in the box, then click “Go.” In the Add -Ins window that appears,

check “Analysis ToolPak,” then click “OK.” Then restart Excel and “Data Analysis”

should appear in the Data toolbar.

To create descriptive statistics for the responses to each quantitative question, Click on “Data

Analysis” then click on “Descriptiv e Statistics.” Put the range of the responses you want

descriptive statistics for in the “Input Range” filed, click on “Grouped By” Columns, check the

“Labels in First Row” box, select “Output Range” and insert a cell address that you want to be the

upper left corner of the output range, check the “Summary statistics” box and click “OK.” Make

sure you include

MGMT 201

Computer Homework #1

Note: Computer homework assignments are individual assignments and must be done without assistance from or collaboration with other members of the class. Collaboration on Computer Homework assignments will result in zero credit for the assignment.

The purpose of this assignment is to help you get started using Excel to perform some of the basic statistical functions we’ve talked about in class. The data set is 100 responses to a survey given last year to MGMT 201 students. The questions in the survey are shown below:

1.	Your age converted to months (e.g., 20 years and 3 months would be 243 months):
2.	Distance in miles from where you lived during high school to UofL:
3.	Your GPA:
4.	Your major or planned major:
5.	Your class year at UofL (F=freshman, S=sophomore, J=junior, R=senior, G=grad, O=other):
6.	Have you taken statistics previously, either in high school or college? (yes or no)
7.	Your level of anxiety about this course on a 1-5 scale with 1 = none and 5 = very high:
8.	Average number of hours per week you work (for pay):
9.	Number of other classes currently enrolled in: Indicate number (indicate 0, 1, 2 etc.)
10.	The average number of hours per week you study during a normal semester.

The spreadsheet with the survey results is posted on Blackboard for this assignment. The data values for the 100 students are arranged in columns in the spreadsheet. The requirements of this assignment are shown below. We will use this data set for at least one other computer homework assignment.

1. The first thing you will have to do create descriptive statistics for the all quantitative variables.

a. To do this, click on the Data tab at the top of the spreadsheet. The far right section of the menu bar should be labeled “Analysis” and should include an item titled “Data Analysis.” If Data Analysis does not appear, click on the File tab at the far left of the tab bar, then click on Options (near the bottom), then click on Add-Ins (next to the bottom of the list at the left of the window), then look at the bottom of the window and you’ll see “Manage:” next to a drop down box. Make sure “Excel Add-ins” appears in the box, then click “Go.” In the Add-Ins window that appears, check “Analysis ToolPak,” then click “OK.” Then restart Excel and “Data Analysis” should appear in the Data toolbar.

To create descriptive statistics for the responses to each quantitative question, Click on “Data Analysis” then click on “Descriptive Statistics.” Put the range of the responses you want descriptive statistics for in the “Input Range” filed, click on “Grouped By” Columns, check the “Labels in First Row” box, select “Output Range” and insert a cell address that you want to be the upper left corner of the output range, check the “Summary statistics” box and click “OK.” Make sure you include

a. the variable in your Input Range.

b. Make sure the count is 100 for all the quantitative variables.

2. Frequency distributions:

a. Have Excel create grouped frequency distribution for the Age (Months) distance

variables. Make sure the frequency distributions include frequency, relative

frequency and cumulative relative frequency.

b. Create Histograms for the Age(Months and distance variables. Briefly describe the

shapes of the histograms.

c. Create ungrouped frequency distributions for the qualitative variables -- “Major,”

the “Class Year” and the “ Stat Class ?”. For the Class Year data, you should have

five values of the variable (Freshman,Sophmore, Junior, Senior, Graduate) and for

the Major data you should have 11 values and for the “Stat Class?” question you

will have two values. Make sure the frequency distributions include frequency,

relative frequency and cumulative relative frequency.

d. Create Histograms for Class Year, Major, and Stat Class variables. Briefly describe

the shape of the histogram.

3. Pie Chart

a. Recode the anxiety variables to 1 = none 2= very low 3=low, 4= high and 5 = very

high

b. Create pie chart on the recoded values. Briefly describe the results of the pie

chart.

Deliverable

 One-page Excel spreadsheet with descriptive statistics for all of the quantitative

variables,

 One-page Excel spreadsheet with the frequency distributions, histogram and

description for the Age, and distance variables .

 One-page Excel spreadsheet with the f requency distributions, histogram and

description for the qualitative variables -- “Major,” the “Class Year” and the “Stat

Class ?” variables.

 One-page Excel pie chart and description for the anxiety variable.

 The page must be landscape oriented (one point ) with your name on top.

Everything must be typed – any hand-written numbers, notes, corrections or

explanations will be ignored.

Due Thursday, Jan 23

at the beginning of class.

a. the variable in your Input Range.

b. Make sure the count is 100 for all the quantitative variables.

2. Frequency distributions:

a. Have Excel create grouped frequency distribution for the Age(Months) distance variables. Make sure the frequency distributions include frequency, relative frequency and cumulative relative frequency.

b. Create Histograms for the Age(Months and distance variables. Briefly describe the shapes of the histograms.

c. Create ungrouped frequency distributions for the qualitative variables -- “Major,” the “Class Year” and the “Stat Class ?”. For the Class Year data, you should have five values of the variable (Freshman,Sophmore, Junior, Senior, Graduate) and for the Major data you should have 11 values and for the “Stat Class?” question you will have two values. Make sure the frequency distributions include frequency, relative frequency and cumulative relative frequency.

d. Create Histograms for Class Year, Major, and Stat Class variables. Briefly describe the shape of the histogram.

3. Pie Chart

a. Recode the anxiety variables to 1 = none 2= very low 3=low, 4= high and 5 = very high

b. Create pie chart on the recoded values. Briefly describe the results of the pie chart.

Deliverable

· One-page Excel spreadsheet with descriptive statistics for all of the quantitative variables,

· One-page Excel spreadsheet with the frequency distributions, histogram and description for the Age, and distance variables.

· One-page Excel spreadsheet with the frequency distributions, histogram and description for the qualitative variables -- “Major,” the “Class Year” and the “Stat Class ?” variables.

· One-page Excel pie chart and description for the anxiety variable.

· The page must be landscape oriented (one point) with your name on top. Everything must be typed – any hand-written numbers, notes, corrections or explanations will be ignored.

Due Thursday, Jan 23th at the beginning of class.