Transcript ch03xt
Guide to Using Excel For Basic Statistical Applications To Accompany Business Statistics: A Decision Making Approach, 6th Ed. Chapter 3: Describing Data Using Numerical Measures By Groebner, Shannon, Fry, & Smith Prentice-Hall Publishing Company Copyright, 2005 Chapter 3 Excel Examples Population Mean Foster City Hotel Mean, Median,and ModeWeigh-In-Motion Percentiles and Quartiles Weigh-In-Motion More Examples Chapter 3 Excel Examples (continued) Measures of Variation Weigh-In-Motion 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 FosterCity.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 Mean, Median and ModeWeigh-In-Motion Issue: Does the WIM scale produce gross weights that are close to the POE weights? Objective: Use Excel to develop histograms for the weights from each scale and to compute the mean, median, and mode for each scale. The data file is Trucks.xls. Mean, Median, and Mode- Weigh-In-Motion Open the data file called Trucks.xls Data File contains 200 trucks. Last row is 201 Mean, Median, and Mode- Weigh-In-Motion Constructing histograms was covered in Chapter 2 (See Capital Credit Union) here is an optional method for determining the frequency distribution and histogram using Pivot Tables Mean, Median, and Mode- Weigh-In-Motion Group the WIM weights by 10,000 pounds starting at 15,000 pounds (See Capital Credit Union example in Chapter 2 for specific steps required to group data. Mean, Median, and Mode- Weigh-In-Motion Highlight the frequencies and click on the Chart Wizard to develop “quickand dirty” histogram Mean, Median, and Mode- Weigh-In-Motion Close Gaps by selecting bars – Right Clicking – select Format Data Series then Options. Mean, Median, and Mode- Weigh-In-Motion Completed Histogram for WIM gross weights – Repeat the process for POE gross weights. Then compute mean, median and modes for both variables Mean, Median, and Mode- Weigh-In-Motion Select Tools – then select Data Analysis Mean, Median, and Mode- Weigh-In-Motion Click on Descriptive Statistics Mean, Median, and Mode- Weigh-In-Motion Define Data Range and select options Mean, Median, and Mode- Weigh-In-Motion Delete un-needed columns Mean, Median, and Mode- Weigh-In-Motion Mean, Median, and Mode Percentiles and QuartilesWeigh-In-Motion Issue: Determine Percentiles and Quartiles for WIM and POE Gross Weights Objective: Use Excel to calculate 10th percentiles and 1st and 3rd quartiles for weigh-in-motion data. Data file is Trucks.xls Percentiles and Quartiles – Weigh-In-Motion Open the data file called Trucks.xls Data File contains 200 trucks. Last row is 201 Percentiles and Quartiles – Weigh-In-Motion Select function wizard – Statistical – then Percentile Percentiles and Quartiles – Weigh-In-Motion Enter Data Range and desired percentile Percentiles and Quartiles – Weigh-In-Motion WIM Percentile Results – Process also repeated for POE Percentiles and Quartiles – Weigh-In-Motion Select Chart Wizard – click on Statistical – then select Quartiles Percentiles and Quartiles – Weigh-In-Motion Enter Data Range and desired quartile Percentiles and Quartiles – Weigh-In-Motion 1st and 3rd quartiles for WIM and POE gross weights Measures of Variation Weigh-In-Motion Issue: Understand the Variation in POE and WIM gross Weights Objective: Use the Excel to compute various measures of variation in WIM and POE Gross Weights Data file is Trucks.xls Measures of Variation – Weigh –In-Motion Open the data file called Trucks.xls Data File contains 200 trucks. Last row is 201 Measures of Variation – Weigh –In-Motion Click on Tools tab – then select Data Analysis – then choose Descriptive Statistics Measures of Variation – Weigh –In-Motion Define data range, output location – request Summary Statistics Measures of Variation – Weigh –In-Motion Standard Deviation, Variance, and Range Measures of Variation – Weigh –In-Motion For POE, select Tools – Data Analysis – Descriptive Statistics Define data range, output location – request Summary Statistics Measures of Variation – Weigh –In-Motion Standard Deviation, Variance, and Range 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)