To Accompany Business Statistics A Decision Making Approach 8th Ed Chapter 3 Describing Data Using Numerical Measures By Groebner Shannon Fry amp Smith PrenticeHall Publishing Company ID: 760262
Download Presentation The PPT/PDF document "Guide to Using Excel 2007 For Basic Stat..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
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
Slide2Chapter 3 Excel Examples
Population Mean Foster City HotelMeasures of Central Tendency Colleges and UniversitiesMeasures of Variation Colleges and Universities
More Examples
Slide3Chapter 3 Excel Examples (continued)
Empirical Rule - Burger N’ Brew
Slide4Population 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
Slide5Population Mean – Foster City Hotel
Open the Excel file called
Foster.xls
Slide6Population Mean – Foster City Hotel
Select Formulas
Select More Functions
Select Statistical
Select Average
Slide7Population Mean – Foster City Hotel
Slide8Population Mean – Foster City Hotel
Optional method:
Select Data
Select Data Analysis
Select Descriptive Statistics from the next dialog box
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
Slide10Population Mean – Foster City Hotel
Mean Revenue
Slide11Measures 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
Slide12Measures of Central
Tendency: Colleges and Universities
Open the file
Colleges and Universities.xls
Slide13Measures 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
Slide14Measures 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
Slide15Measures of Central Tendency: Colleges and universities
Slide16Descriptive 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
Slide17Descriptive Statistics –
Colleges and Universities
Open the file
Colleges and Universities
Slide18Descriptive Statistics –
Colleges and Universities
Select the Data tab
Select Data Analysis
Select Descriptive Statistics
OK
Slide19Descriptive Statistics –
Colleges and Universities
Input Range =
L1:L719
Grouped by Columns
Labels in First Row
New Worksheet Ply
Summary Statistics
Slide20Descriptive Statistics –
Colleges and Universities
Mean
Median
Mode
Slide21Measures 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
Slide22Measured of Variation
– Colleges and Universities
Open the file
Colleges and Universities
Slide23Measures of Variation
– Colleges and Universities
Input Range =
L1:L719
Grouped by Columns
Labels in First Row
New Worksheet Ply
Summary Statistics
Slide24Measures of Variation
– Colleges and Universities
Select the Data tab
Select Data Analysis
Select Descriptive Statistics
OK
Slide25Measures of Variation
– Colleges and Universities
Input Range =
L1:L719
Grouped by Columns
Labels in First Row
New Worksheet Ply
Summary Statistics
Slide26Measures of Variation
– Colleges and Universities
Standard Deviation
Variance
Range
Slide27Empirical 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
Slide28Empirical Rule – Burger N’ Brew
Open the data file called BurgerNBrew.xls. The file contains data for 365 days
Slide29Empirical Rule – Burger N’ Brew
First develop histogram – Class width = 2. Form bins
Slide30Empirical Rule – Burger N’ Brew
Select the Data Tab
Select Data Analysis
Select Histogram
OK
Slide31Empirical Rule – Burger N’ Brew
Input Range- Chilli-Burgers Sold
Bin Range – the Bins just created
Select New Worksheet Ply
Select Chart Output
OK
Slide32Empirical Rule – Burger N’ Brew
This is the default output – now to reformat the Histogram a bit
Slide33Empirical Rule – Burger N’ Brew
Looks better?
Now to compute the statistics
Slide34Empirical Rule – Burger N’ Brew
Select the Data Tab
Select Data Analysis
Select Descriptive Statistics
OK
Slide35Empirical Rule – Burger N’ Brew
Input Range is the column to be analyzed
Select New Workbook
Select Summary Statistics
Slide36Empirical 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)