Statistics for Decision Making

Statistics for Decision Making Excel for Statistics: An Overview

Student Objectives • Perform basic Excel tasks • Building formulae • Copy/paste • Insert rows/columns • Move • Modify displays • Use Excel functions for basic statistics • Descriptives: mean, median, standard deviation, quantiles • Integrate with presentation features • Summarize guidelines for spreadsheet design and development

First, . . . • Turn in your homework • Histograms • Bar charts • Quantiles for KIVZ • Old stuff (look at the roster) • eMail address • Questionnaire info • Monday: meet in BU 221

Here’s an Exercise • Goal: • Demonstrate Excel formulae and display concepts • Application to data analysis • But not a demo of Excel’s statistics capability • Given income values: $76,723 $37,452 $22,557 $60,155 • Calculate average & standard deviation • Create attractive tabular display

Working the Exercise (Quick & Dirty) • Column A: type values (no $ or comma) • Use Excel’s S tool to sum values • Divide sum by n (use COUNT, not 4) • Column B: subtract x-bar (use absolute addressing) from first x value • Copy result down 3 cells • Column C: square first deviation and copy result down 3 cells • Sum squared deviations • Divide result by n-1 • Take square root of the result

Working the Exercise (Cleaning it Up) • Insert four rows at top and 1 column at left • Create column headings • Center, boldface, and italicize column headings • Adjust column widths • Format raw data values as currency with 0 decimal places • Format intermediate calculations appropriately • Add appropriate labels for x-bar and s • Create an overall centered title for table

Now, Let’s Use Excel More Efficiently • KIVZ data: analysis of McCall incomes • Download dataset from the Web • Don’t open within browser • Instead, save to disk and then open in Excel • Enter data into Column A (type or copy from original worksheet)

Using Excel’s Built-In Functions • Summarize central tendency • Mean (AVERAGE) • Median (MEDIAN) • Summarize spread • Standard deviation (STDEV) • Range (MAX and MIN) • Calculate also • Percentiles • Cumulative frequency

Spreadsheet Guidelines • Save your work often (once per step) • Keep in mind that spreadsheets are typically reused • Updated data • Modified to fit similar situations • Use Data Analysis tool only as quick & dirty • Avoid using constants in formulae • Keep data separate from cells with calculations • Consider locking cells involving calculations • Do whatever it takes not to type data

Odds & Ends • Inference examples • Hypothesis testing • Estimation • Some notes on variation • What’s a “variance”? • The CV: answering the question “How much is much?” • Where is the variation? • Median versus mean • Skew • Using the histogram to address this question • Discrepancies in quantile computations

Homework • Use Excel to calculate average income and standard deviation for Channel 6 as demonstrated in first exercise • Use Excel statistical functions to calculate descriptive statistics for overall McCall viewing time • More info to be posted at website

Statistics for Decision Making