Good Statistics with Microsoft Excel

1
Good Statisticswith Microsoft Excel

• Howard Grubb, Roger Stern
• and Colin Grayer
• Department of Applied Statistics
• 6th June 2001

2
Statistical Activities

• Statistics is concerned with data
• management and analysis
• Applied Statistics Advisory service to support
  research across the University
• Excel is widely used
• Statistics teaching by many departments
• Excel can be a useful starting point
• most students are familiar with Excel
• Data from other sources
• administrative workloads, exam marks

3
Contents

• Why Excel?
• Why this seminar?
• Using standard Excel
• Extending it
• Good Practice
• Conclusions
• Discussion

4
Why Excel?

• Very popular
• everyone knows it, except perhaps
  statisticians!
• Good for exploratory/descriptive statistics
• graphs and tables - see later
• Dynamic
• both graphics and calculations
• change data and analysis updates
• Easy to extend
• powerful (visual) language associated with Excel

5
Practical examples

• Rice survey and bank data
• list format
• (by factor)
• Administrative timetabling
• restricting data
• multiple, linked sheets (one copy of data)
• (extending Excel programs vs packages)
• Exploratory - anaerob2.xls
• dynamic calculation

6
Software Strategy

• Mixed software strategy
• students are comfortable within Windows
• sometimes more so than staff!
• so mixing software for statistical applications
  is now straightforward
• training can concentrate on the statistical ideas
• and not simply on mastering the software
• Easy to add Excel to existing software
• adds to, does not need to replace existing
  statistical software (e.g. Minitab, SPSS )

7
Why ignore Excel?

• Does not encourage good (statistical) practice
• provides the freedom to make a mess
• from data entry onwards
• (Advanced) statistical facilities have problems
• not all numerically stable
• not sufficiently powerful, hence usually still
  need another (statistics) package
• Why add yet another package?

8
On balance

• Suggest the case for adding Excel is strong
• to our Advisory support
• and encouraging staff and students to use it well
• Is sufficiently strong
• to warrant this seminar
• Otherwise
• many people use it extensively, by default
• perhaps spending longer, and not doing their work
  as efficiently as they might

9
Excel in action

• Tabulation
• called pivot tables
• And dynamic nature
• can make some teaching easy
• Demonstration
• tables - bank
• plots - templates

10
Adding good statistics

• Excel encourages list format
• obvious layout of data for any statistics package
• But some Excel statistics and graphics
• do not use the data in this form
• So to encourage good statistics with Excel we
  have our own Excel add-in
• written within Department of Applied Statistics
• other Add-Ins extend the facilities
• ours improves the existing ones

11
http//www.rdg.ac.uk/snsgrubb/excel

• Manage menu
• including stack and unstack
• Graphics menu
• including x-y plot by a factor
• boxplots
• Statistics menu
• including describe, summary, t-interval
• uses solid algorithms
• General
• data in list format
• handles missing values
• links (so dynamic)
• handles filters (hidden rows)

12
Demonstration

• Rice
• x-y plot adding lines (UnStack)
• boxplots by
• UnStack
• Dynamic plots
• Help and good-practice guidelines

13
Adding to Excel

• Easy to do now offer training on this
• Facilities for adding are easier than in many
  statistics packages
• Results in an environment
• easy to distribute
• available on most machines
• Example
• timetable - program
• adaptive sampling quick demonstration
• SSC-Stat - package

14
Good-practice guidelines

• Validated data entry
• When to move to a database package?
• Keep your data safe!
• Exploratory analysis/graphics
• Basic statistics
• When to move to a statistics package
• When to stop programming?!

15
Expertise/resources at Reading

• Training courses could be provided
• based on courses we already run for external
  clients
• Excel for Statistics, what you can do (3 days,
  including macros)
• Basic statistics using Excel (2 days, from
  January 2002)
• Scientific data management using MS Access (2-3
  days)
• Support for Good Practice
• local add-in
• many other resources see web-page
• Advisory Service