Transcript Overview of Excel for Statistics

Statistics for
Decision Making
QM 2113 - Spring 2002
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