Download
1 / 11
110 likes | 242 Vues
This course provides an overview of using Excel for fundamental statistical tasks, enabling students to perform basic calculations and create clear data displays. You'll learn how to build formulas, utilize Excel functions for descriptive statistics (mean, median, standard deviation), and effectively format and present your data. By engaging in hands-on exercises with real datasets, you will enhance your proficiency in data analysis and spreadsheet design. Homework will reinforce your understanding through practical applications, establishing a strong foundation in statistics.
E N D
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
