Statistics for Decision Making

1
Statistics for Decision Making

• Excel for Statistics An Overview

2
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

3
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

4
Heres an Exercise

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

5
Working the Exercise (Quick Dirty)

• Column A type values (no or comma)
• Use Excels 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

6
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

7
Now, Lets Use Excel More Efficiently

• KIVZ data analysis of McCall incomes
• Download dataset from the Web
• Dont open within browser
• Instead, save to disk and then open in Excel
• Enter data into Column A (type or copy from
  original worksheet)

8
Using Excels Built-In Functions

• Summarize central tendency
• Mean (AVERAGE)
• Median (MEDIAN)
• Summarize spread
• Standard deviation (STDEV)
• Range (MAX and MIN)
• Calculate also
• Percentiles
• Cumulative frequency

9
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

10
Odds Ends

• Inference examples
• Hypothesis testing
• Estimation
• Some notes on variation
• Whats 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

11
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