1 / 17

Data Analysis in Excel

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. Data Analysis in Excel. Analysis of Uncertainty. Learning Objectives.

dalton-little
Télécharger la présentation

Data Analysis in Excel

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related