Guide to Using Excel 2007 For Basic Statistical

Guide to Using Excel 2007 For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 8 th Ed. Chapter 3: Describing Data Using Numerical Measures By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2011

Chapter 3 Excel Examples n n n Population Mean Foster City Hotel Measures of Central Tendency Colleges and Universities Measures of Variation Colleges and Universities More Examples

Chapter 3 Excel Examples (continued) n Empirical Rule Burger N’ Brew

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

Population Mean – Foster City Hotel Open the Excel file called Foster. xls

Population Mean – Foster City Hotel • Select Formulas • Select More Functions • Select Statistical • Select Average

Population Mean – Foster City Hotel

Population Mean – Foster City Hotel Optional method: • 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

Population Mean – Foster City Hotel Mean Revenue

Measures of Central Tendency – Colleges and Universities Issue: What is the distribution of out-of-state tuition costs for US colleges and universities Objective: Use Excel 2007 to develop a histograms for tuition costs The data file is Colleges and Universities. xls

Measures of Central Tendency: Colleges and Universities Open the file Colleges and Universities. xls

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: L 1: L 719 • Bin Range: O 11 • Select New Worksheet Ply • OK

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

Measures of Central Tendency: Colleges and universities

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

Descriptive Statistics – Colleges and Universities Open the file Colleges and Universities

Descriptive Statistics – Colleges and Universities • Select the Data tab • Select Data Analysis • Select Descriptive Statistics • OK

Descriptive Statistics – Colleges and Universities • Input Range = L 1: L 719 • Grouped by Columns • Labels in First Row • New Worksheet Ply • Summary Statistics

Descriptive Statistics – Colleges and Universities Mean Median Mode

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

Measured of Variation – Colleges and Universities Open the file Colleges and Universities

Measures of Variation – Colleges and Universities • Input Range = L 1: L 719 • Grouped by Columns • Labels in First Row • New Worksheet Ply • Summary Statistics

Measures of Variation – Colleges and Universities • Select the Data tab • Select Data Analysis • Select Descriptive Statistics • OK

Measures of Variation – Colleges and Universities • Input Range = L 1: L 719 • Grouped by Columns • Labels in First Row • New Worksheet Ply • Summary Statistics

Measures of Variation – Colleges and Universities Standard Deviation Variance Range

Empirical Rule Burger N’ Brew Issue: Analyze the Phoenix Burger Sales Distribution Objective: 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 Burger. NBrew. xls

Empirical Rule – Burger N’ Brew Open the data file called Burger. NBrew. xls. The file contains data for 365 days

Empirical Rule – Burger N’ Brew First develop histogram – Class width = 2. Form bins

Empirical Rule – Burger N’ Brew • Select the Data Tab • Select Data Analysis • Select Histogram • OK

Empirical Rule – Burger N’ Brew • Input Range- Chilli-Burgers Sold • Bin Range – the Bins just created • Select New Worksheet Ply • Select Chart Output • OK

Empirical Rule – Burger N’ Brew This is the default output – now to reformat the Histogram a bit

Empirical Rule – Burger N’ Brew Looks better? Now to compute the statistics

Empirical Rule – Burger N’ Brew • Select the Data Tab • Select Data Analysis • Select Descriptive Statistics • OK

Empirical Rule – Burger N’ Brew • Input Range is the column to be analyzed • Select New Workbook • Select Summary Statistics

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)