Transcript ch03_xl_ 2003_tutorial

Guide to Using Excel 2003 For Basic
Statistical Applications
To Accompany
Business Statistics: A Decision Making
Approach, 7th Ed.
Chapter 3:
Describing Data Using Numerical
Measures
By
Groebner, Shannon, Fry, & Smith
Prentice-Hall Publishing Company
Copyright, 2008
Chapter 3 Excel 2003
Examples
 Population
Mean Foster City Hotel
 Mean, Median, Mode Standard
DeviationStock Prices – US Companies
More Examples
Chapter 3 Excel Examples (continued)

Empirical Rule Burger N’ Brew
Population Mean
Foster City Hotel
Issue:
Determine the mean nightly revenue for
the Foster City Hotel.
Objective:
Use Excel to calculate the population
mean revenue Data File is FosterCity.xls
Population Mean – Foster City Hotel
Open the Excel file called
Foster.xls
Population Mean – Foster City Hotel
Option 1 – Use the Excel
Average function.
Click the function wizard –
Select Statistical category –
Select Average
Population Mean – Foster City Hotel
Specify data range Click OK
Population Mean – Foster City Hotel
Population Mean
Population Mean – Foster City Hotel
Optional Method:
Select Tools, then
Data Analysis
Population Mean – Foster City Hotel
Select Descriptive
Statistics
Population Mean – Foster City Hotel
Define Data Range
and select options
Population Mean – Foster City Hotel
Mean Revenue
Population Mean – Foster City Hotel
To calculate means
for all three
variables
Population Mean – Foster City Hotel
Mean, Median, Mode, and
Standard Deviation Stock Prices
Issue:
Describe the distribution of stock prices
for U.S. Companies
Objective:
Use Excel to develop histograms for the
U.S. Companies’ stock prices and to compute
the mean, median, mode and standard deviation
for stock price. The data file is US Companies
2005.xls.
Descriptive Statistics- Stock Prices
Open the data
file called US
Companies
2005.xls
Data File contains
6,992 stocks. Last
row is 6,993
Descriptive Statistics- Stock Prices
Constructing histograms
was covered in Chapter 2
(See Capital Credit Union)
Begin by developing thebins
(upper limit of each class.)
Descriptive Statistics- Stock Prices
Click on Tools > Data
Analysis > Histogram.
Descriptive Statistics- Stock Prices
Fill in data and bin ranges –
check labels – location for
output – check Chart Output
Descriptive Statistics- Stock Prices
Review Steps in Chapter 2
to complete histogram
Descriptive Statistics- Stock Prices
Completed Histogram for
Stock Prices. Then
compute mean, median and
modes for stock price.
Descriptive Statistics- Stock Prices
Select Tools – then select
Data Analysis > Descriptive
Statistics
Descriptive Statistics- Stock Prices
Define Data Range
and select options
Descriptive Statistics- Stock Prices
Mean
Median
Mode
Standard Deviation
Empirical Rule Burger N’ Brew
Issue:
Analyze the Phoenix Burger Sales
Distribution
Objective:
Use the Excel to compute graphs and
numerical measures necessary for using the
empirical rule to analyze sales at Burger N’ Brew
Data file is BurgerNBrew.xls
Empirical Rule – Burger N’ Brew
Open the data file
called
BurgerNBrew.xls
Data File contains
sales for 365 days.
Last row is 366.
Empirical Rule – Burger N’ Brew
First, develop
histogram – class
width = 2. Form
Bins (upper limit
of each class)
Refer to Example 2-6 for detailed
steps in developing the
histogram
Empirical Rule – Burger N’ Brew
Click on Tools tab – then Data
Analysis – then Histogram
Empirical Rule – Burger N’ Brew
Define input
range, bin
range, output
location –
check Chart
Output
Empirical Rule – Burger N’ Brew
Excel default output – See Capital
Credit Union example Chapter 2 for
specific steps to enhance the
histogram – close gaps, label , etc.
Empirical Rule – Burger N’ Brew
Finished Histogram – Bell Shaped
Distribution
Empirical Rule – Burger N’ Brew
To compute numerical measures –
Click Tools – Data Analysis –
Descriptive Statistics
Empirical Rule – Burger N’ Brew
Define data range, output
location – request
Summary Statistics
Empirical Rule – Burger N’ Brew
Mean and Standard Deviation
Empirical Rule:
68% within 15.12 + (1)3.13
95% within 15.12 + (2)3.13
99.7% within 15.12 + (3)3.13)