Guide to Using Excel 2007 For Basic Statistical Applications - PowerPoint Presentation

Slide1

Guide to Using Excel 2007 For Basic Statistical Applications

To AccompanyBusiness Statistics: A Decision Making Approach, 8th Ed.Chapter 3:Describing Data Using Numerical MeasuresByGroebner, Shannon, Fry, & SmithPrentice-Hall Publishing CompanyCopyright, 2011

Slide2

Chapter 3 Excel Examples

Population Mean Foster City HotelMeasures of Central Tendency Colleges and UniversitiesMeasures of Variation Colleges and Universities

More Examples

Slide3

Chapter 3 Excel Examples (continued)

Empirical Rule - Burger N’ Brew

Slide4

Population Mean Foster City Hotel

Issue: Determine the mean nightly revenue for the Foster City Hotel.Objective: Use Excel 2007 to calculate the population mean revenue Data File is Foster.xls

Slide5

Population Mean – Foster City Hotel

Open the Excel file called

Foster.xls

Slide6

Population Mean – Foster City Hotel

Select Formulas

Select More Functions

Select Statistical

Select Average

Slide7

Population Mean – Foster City Hotel

Slide8

Population Mean – Foster City Hotel

Optional method:

Select Data

Select Data Analysis

Select Descriptive Statistics from the next dialog box

Slide9

Population Mean – Foster City Hotel

Highlight Column C to fill the Input Range Box

Select either New Worksheet or Output Range to decide where the output is placed

Select Summary Statistics

OK

Slide10

Population Mean – Foster City Hotel

Mean Revenue

Slide11

Measures of Central Tendency – Colleges and Universities

Issue: What is the distribution of out-of-state tuition costs for US colleges and universitiesObjective: Use Excel 2007 to develop a histograms for tuition costsThe data file is Colleges and Universities.xls

Slide12

Measures of Central

Tendency: Colleges and Universities

Open the file

Colleges and Universities.xls

Slide13

Measures of Central

Tendency: Colleges and Universities

Insert a column for the Bins – label it appropriately

Select the Data Tab

Select Data Analysis

Select Histogram

Input Range:

L1:L719

Bin Range:

O1:O11

Select New Worksheet Ply

OK

Slide14

Measures of Central

Tendency: Colleges and Universities

Enlarge the Histogram by pulling on the “handles”

Right click on bars and Use the Format Data Series Option Setting the Gap Width to zero

Slide15

Measures of Central Tendency: Colleges and universities

Slide16

Descriptive Statistics: Colleges and Universities

Issue: Determine the Descriptive Statistics for out-of-state tuition costs for US colleges and universitiesObjective: Use Excel 2007 to calculate the descriptive statistics for out-of-state tuition costs. Data file is Colleges and Universities

Slide17

Descriptive Statistics –

Colleges and Universities

Open the file

Colleges and Universities

Slide18

Descriptive Statistics –

Colleges and Universities

Select the Data tab

Select Data Analysis

Select Descriptive Statistics

OK

Slide19

Descriptive Statistics –

Colleges and Universities

Input Range =

L1:L719

Grouped by Columns

Labels in First Row

New Worksheet Ply

Summary Statistics

Slide20

Descriptive Statistics –

Colleges and Universities

Mean

Median

Mode

Slide21

Measures of Variation: Colleges and Universities

Issue: Determine the Measures of Variation for out-of-state tuition costs for US colleges and universitiesObjective: Use Excel 2007 to calculate Measures of Variation for out-of-state tuition costs. Data file is Colleges and Universities

Slide22

Measured of Variation

– Colleges and Universities

Open the file

Colleges and Universities

Slide23

Measures of Variation

– Colleges and Universities

Input Range =

L1:L719

Grouped by Columns

Labels in First Row

New Worksheet Ply

Summary Statistics

Slide24

Measures of Variation

– Colleges and Universities

Select the Data tab

Select Data Analysis

Select Descriptive Statistics

OK

Slide25

Measures of Variation

– Colleges and Universities

Input Range =

L1:L719

Grouped by Columns

Labels in First Row

New Worksheet Ply

Summary Statistics

Slide26

Measures of Variation

– Colleges and Universities

Standard Deviation

Variance

Range

Slide27

Empirical Rule - Burger N’ Brew

Issue: Analyze the Phoenix Burger Sales DistributionObjective: Use Excel 2007 to compute graphs and numerical measures necessary for using the empirical rule to analyze sales at Burger N’ Brew Data file is BurgerNBrew.xls

Slide28

Empirical Rule – Burger N’ Brew

Open the data file called BurgerNBrew.xls. The file contains data for 365 days

Slide29

Empirical Rule – Burger N’ Brew

First develop histogram – Class width = 2. Form bins

Slide30

Empirical Rule – Burger N’ Brew

Select the Data Tab

Select Data Analysis

Select Histogram

OK

Slide31

Empirical Rule – Burger N’ Brew

Input Range- Chilli-Burgers Sold

Bin Range – the Bins just created

Select New Worksheet Ply

Select Chart Output

OK

Slide32

Empirical Rule – Burger N’ Brew

This is the default output – now to reformat the Histogram a bit

Slide33

Empirical Rule – Burger N’ Brew

Looks better?

Now to compute the statistics

Slide34

Empirical Rule – Burger N’ Brew

Select the Data Tab

Select Data Analysis

Select Descriptive Statistics

OK

Slide35

Empirical Rule – Burger N’ Brew

Input Range is the column to be analyzed

Select New Workbook

Select Summary Statistics

Slide36

Empirical Rule – Burger N’ Brew

And the statistics are:

The Empirical rule tells us –

68% falls between 15.12 +/- 3.13

95% falls between 15.12 +/- 2(3.13)

99% falls between 15.12 +/- 3(3.13)