1. Data Analysis in Excel • ACADs (08-006) Covered • Keywords average, median, min, max, standard deviation, variable, varp, standardize, normal distribution, norminv, normsinv • Description • Supporting Material

2. Data Analysis in Excel Analysis of Uncertainty

3. Learning Objectives Learn to use statistical Excel functions: average, median, min, max, stdev, var, varp, standardize, normdist, norminv, normsinv

4. General Excel Behavior - Analyzes the range of cells you specify - Skips blank cells

5. Mean Example: =AVERAGE(B72:B81) Excel =AVERAGE(cellrange) Sample Population

6. Mode Value that occurs most often in discretized data Excel Example: =MODE(cellrange) =MODE(B2:B81) If tie, reports first value in list

7. Median The middle value in sorted data Excel =MEDIAN(cellrange) Example: =MEDIAN(D2:D81) Note: When using this command, there is no need to sort the data first.

8. Maximum, Minimum, and Range Excel Example: =MIN(cellrange) =MIN(D2:D81) =MAX(cellrange) =MAX(D2:D81) There is no explicit command to find the range. However, it can be easily calculated. = MAX(D2:D81) - MIN(D2:D81)

9. Standard Deviation and Variance Population Sample Excel =STDEVP(cellrange) =STDEV(cellrange) =VARP(cellrange) =VAR(cellrange) Variance = s2 Variance = s2

10. Review:The Normal Distribution mean The normal distribution is sometimes called the “Gauss” curve. RF Relative Frequency x

11. Standard Normal Cumulative Distribution area from minus infinity to z NOT 0 to z, like Z-table Excel Example: =NORMSDIST(z) =NORMSDIST(1.0) =0.8413

12. Exam Grade Histogram

13. Excel Example Normal distribution with =5, =0.2 Find area from 4.8 to 5.4 • Solution 1: =STANDARDIZE(4.8,5,0.2) Gives -1 =STANDARDIZE(5.4,5,0.2) Gives 2 =NORMSDIST(2)-NORMSDIST(-1) = 0.8186 • Solution 2: =NORMDIST(5.4,5,0.2,TRUE)- NORMDIST(4.8,5,0.2,TRUE) = 0.8186

14. Inverse Problem Given ,  and probability, find x =NORMINV(prob,mean,stddev) Given probability, find z =NORMSINV(prob) Note: The probability is the area under the curve from minus infinity to x (or z)

15. Inverse Problem:Example 1 A batch of bolts have length =5.00 mm, =0.20 mm. 99% of the bolts are shorter than what length? • Solution 1: =NORMINV(0.99,5,0.2) gives 5.47 mm • Solution 2: =NORMSINV(0.99) = 2.33 5.00+0.20*2.33 = 5.47 mm

16. Inverse Problem:Example 2 A batch of bolts have length =5.00 mm, =0.20 mm. The bolt length is specified as 5.00 mm ± tolerance. What is the value of the tolerance such that 99% of the bolts are encompassed? Solution: =NORMINV(0.995,5,0.2) = 5.52 mm =NORMINV(0.005,5,0.2) = 4.48 mm Tolerance = 5.52 - 5.00 = 0.52 mm Note: It is symmetrical; therefore 0.5% on either side

17. Bolt Specification