Class 19'1: Data Analysis in Excel

1
Class 19.1 Data Analysis in Excel

• Analysis of Uncertainty

2
Learning Objectives

• Learn to use statistical Excel functions
• average, median, min, max, stdev, stdevp, var,
  varp, standardize, normdist, normsdist, norminv,
  normsinv

3
Excel Behavior

• Excel
• Analyzes cell range
• Skips blank cells

4
Computing the Mean

• Sum xi divide by n (or N for population mean)
• Excel
• AVERAGE(cellrange)

5
Computing the Mode

• Value that occurs most often in discretized data
• Excel
• MODE(cellrange)
• Reports first value seen if tie

6
Computing the Median

• The middle value in sorted data
• Excel
• MEDIAN(cellrange)

7
Computing the Range

• Range is min to max values
• Excel
• MIN(cellrange)
• MAX(cellrange)

8
Computing the Standard Deviation

• Std. Dev. is Square-Root of Variance
• Excel
• STDEV(cellrange) - sample
• STDEVP(cellrange) - population
• VAR(cellrange) - sample
• VARP(cellrange) - population

9
Example - Exam Grades

• Data set grades.xls
• 78 students, 1 did not take exam
• Verify the following
• Mean is 79.41
• Modes are 79, 84 85 all occur 6 times
• BE SURE YOU KNOW HOW MODE() WORKS IN EXCEL!!
• Median is 79.5
• median close to mean suggests no major outliers
• Remember student who did not take exam is not
  included in data

10
Example Cont.

• Given max is 99, min is 60
• Range is 99-60 39
• Population variance is 60.7
• Population standard deviation is 7.79
• Sample variance is 61.49
• Sample standard deviation is 7.84

11
Team Exercise - 8 Min.

• Collect distance (in miles) of team members
  hometown from B/CS and compute the mean distance
  for team.
• Write your team number and mean distance on the
  board.
• Enter data as columns in Excel
• Compute mean, mode, median, max, min, range,
  sample variance and sample standard deviation
  using Excel commands

12
The Normal Distribution

• Parameters are mean and standard deviation
• Symmetrical about mean

mean, µ
13
Standard Normal

• Define
• The Z transform

Area 1.00
14
Analysis of Normally-Distributed Data in Excel

• Z transform
• STANDARDIZE(x,mean,stddev)
• Standard Normal cumulative distribution
• NORMSDIST(z)
• area from -infinity to z, not 0 to z like Z-table
  in your Foundations textbook

15
Normal Data in Excel

• Avoiding Z transform
• NORMDIST(x,mean,stddev,type)
• Computes Z transform in function
• TypeTRUE if area from -infinity to x
• TypeFALSE if distribution value at x
• Can use to plot the distribution

16
Exam Grade Histogram
17
Excel Example

• Normal distribution with ?5, ?0.2
• Find area from 4.8 to 5.4
• Excel
• Always subtract since area from -infinity to z
• Z-transform area from -1 to 2
• NORMSDIST(2)-NORMSDIST(-1) 0.8186
• NORMDIST(5.4,5,0.2,TRUE)- NORMDIST(4.8,5,0.2,TRU
  E) 0.8186

18
Inverse Problem

• Given ?, ? and probability, find x or z
• Excel
• NORMINV(prob,mean,stddev)
• NORMSINV(prob)
• Probability from -infinity to x or z

19
Example

• We know from the tolerances lecture that
  manufacturing to an exact size is impossible.
  Consider a bolt manufacturer whose bolts have
  length ?5.00 mm, ?0.20 mm.
• What length should be put in the data sheet so
  that 99 of the bolts are shorter than this
  value?
• NORMINV(0.99,5,0.2) 5.47 mm
• NORMSINV(0.99) 2.33
• 5.47 5.000.202.33

20
Example

• Draw a schematic sketch of the bolt and dimension
  the length with a tolerance determined such that
  99 of the bolts fall into this range?
• Symmetrical 0.5 on either side
• NORMINV(0.995,5,0.2) 5.52 mm
• NORMINV(0.005,5,0.2) 4.48 mm
• Note this is /- 2.6?

21
Bolt Specification
22
Homework

• A sample was taken of ENGR 112 students and it
  was found that the cost of the calculators they
  use in the class was normally distributed. The
  mean cost of a calculator is 120 with a standard
  deviation of 10. Assuming that 450 students
  will be enrolled in ENGR 112 this fall, how many
  students would you predict spent a) more than
  110 b) less than 80 c) between 80 and 110.
  d) What price range (centered about the mean)
  would include 90 of the ENGR 112 calculators?

23
Homework (Due 2/11/03)

• Assignment 5 (INDIVIDUAL)
• Prob. 1 Complete the calculator problem - each
  member of the team should submit their own
  solution.
• Prob. 2 Foundations 9.7
• Both problems should be worked using Excel
  exclusively.
• Standard cover sheet required.

24
Think-Pair-Share

• In the next 1 minute as an Individual
• if I only answer one question . . . specifically
  what dont you understand about todays topic at
  least 3 things
• Now take 2 minutes
• to merge your list with the person sitting next
  to you AND add 1 new item to the list
• In the next 5 minutes
• share the results with the other half of your
  team, delete questions that you can answer for
  each other, AND prioritize the remaining
  questions your list