groebner business statistic 8ed ch06 xl 2007 tutorial

Guide to Using Excel 2007 For Basic

Statistical Applications

To Accompany

Business Statistics: A Decision Making

Approach, 8th Ed.

Chapter 6:

Introduction to Continuous Probability

Distributions

By

Groebner, Shannon, Fry, & SmithPrentice-Hall Publishing Company

Copyright, 2011

Chapter 6 Excel Examples

Using the Normal Distribution-State Bank and Trust

Exponential Distribution–Haines Internet Service

Creating a Standard Normal Table

Standard Normal Distribution-

State Bank and TrustIssue: State bank managers have studied the

time customers spend at the bank on business.

They plan to offer a gift certificate to any

customer who is required to spend over 30

minutes on bank business.

Objective: Use Excel 2007 to create a histogram

and determine the probability that a gift

certificate will be given to any customer. The

data file is State Bank.xls.

Standard Normal – State Bank and Trust

Open file StateBank.xls

Standard Normal – State Bank and Trust

Using the Data Analysis –Histogram Function, Build the Histogram as indicated. Select Chart Output

Standard Normal – State Bank and Trust

Format as per Chapter 2

Standard Normal – State Bank and Trust

Using the Chart Tools option, Format the histogram, insert titles and labels

Next, determine probability of a service time exceeding 30 minutes.

Standard Normal – State Bank and Trust

Using the Chart Tools option, Format the histogram, insert titles and labels

Next, determine probability of a service time exceeding 30 minutes.

Standard Normal – State Bank and Trust

•Select Formulas

•Statistical

•Select Formulas

•Select Statistical

•Select NormDist

Standard Normal – State Bank and Trust

Insert:

•X = 30

•Mean = 22.14

•Standard Dev = 6.09

•Cumulative – true

Probability P(X <= 30) = .9016

Exponential Distribution-

Haines Internet Services

Issue: Assume the time between connect attempts

averages two minutes and the distribution of time

between attempts is assumed to be exponential.

Objective: Use Excel 2007 to compute the probability

that that the time between connect attempts will be

less than 45 seconds (.75 minutes).

Exponential Distribution - Haines Internet Services

•Select the Formulas tab

•Select More Functions

•Select Statistical

•Select EXPONDIST

Exponential Distribution - Haines Internet Services

Input:

•X = .75

•Lambda = .50

•True

•Cumulative Exponential Probability

Creating A Standard Normal

Distribution Table

Issue: The Standard Normal tables in the

Appendix of this text were created using Excel.

Objective: Use Excel to generate a standard

normal table for z values below zero.

Creating the Standard Normal Table

Z-value, second decimal place

Z-value integer and first decimal place

Creating the Standard Normal Table

Probabilities correspond to area on the standard normal table between the Z value and the mean = 0

Formula:•ABS function is for absolute value.•Mean for NORMDIST function is -0.0 -.01 = - .01 •Notice use of absolute cell references to allow formula to be copied across and down to finish the table