| Mean | =AVERAGE() | |||
|---|---|---|---|---|
| Weighted Mean |
=SUMPRODUCT(weights, values) / n Where n = SUM(weights) |
|||
| Median | =MEDIAN() | |||
| Mode | =MODE.SNGL() or =MODE.MULT() | |||
| Range | =MAX()-MIN() | |||
| Class Width | =range / number of classes | |||
| Variance | Sample: | =VAR.S() | ||
| Population: | =VAR.P() | |||
| Standard Deviation (SD) | ||||
| Sample: | =STDEV.S() | |||
| Population: | =STDEV.P() | |||
| Or,=SQRT(variance) | ||||
| Skewness |
=3*(mean - median) / SD |
|||
| Coefficient of Variation | ||||
| =SD / mean * 100 | ||||
| Empirical Rule | 0.68, 0.95, 0.997 |
|||
| Chebyshev | = 1 - 1 / SD^2 |
|||
| Percentile | i = percentile/100 * n Round up if decimal; average if whole. |
|||
| IQR, Outliers | = Q3 - Q1 |
|||
|
Upper Outlier =Q3 + 1.5 * IQR |
||||
|
Lower Outlier =Q1 − 1.5 * IQR |
||||
| Probability | P(A) P(A or B) |
= A / Total = P(A) + P(B) - P(Both) |
||
|
P(A and B) P(A and B) P(A | B) P(A | B) |
= P(A) * P(B) = P(A) * P(B|A) = P(A and B) / P(B) = P(A|B)*P(B) P(A|B)P(B)+P(A|Bc)P(Bc) = P(B|A)*P(A) / P(B) |
Independent Dependent Conditional Bayes' Rule |
||
| Permutation | =PERMUT(n,r) | |||
| Combination | =COMBIN(n,r) | |||
| Discrete Distributions | ||||
| Expected Value: =SUMPRODUCT(x,P(x)) Variance: =SUM(x^2 * p) - (expected value^2) SD: =SQRT(discrete variance) |
||||
| Binomial Discrete Distributions | ||||
|
|
Mean or Expected Value: =n*p Variance: =n*p*(1 - p) SD=SQRT(variance) Probability: =BINOM.DIST(successes,trials,probability,cum) |
|||
| Z Score | =STANDARDIZE(x,mean,SD) =(x - mean) / SD |
|||
| Normal Distributions | |||
|---|---|---|---|
|
Probability: =NORM.DIST(x,mean,SD,TRUE) Where TRUE means cumulative |
|||
|
Z to Probability: =NORM.S.DIST(z score,1) |
|
||
|
|
Probability to Z: =NORM.S.INV(probability) Probability to X: =NORM.INV(probability,mean,SD) |
|
|
| Sampling from Normal Distribution, Mean (Central Limit Theorem) | |||
| Standard Error: | =SD / SQRT(n) | ||
|
Probability: =NORM.DIST(x,mean,SD/SQRT(n),1) |
=NORM.DIST() | ||
| Where 1 means cumulative | |||
| Sampling from Normal Distribution, Proportion | |||
| Validation: n*p ≥ 5 and n*(1-p) ≥ 5 | |||
| z = (pS - p) / SQRT(p*(1 - p) / n) | |||
|
Probability: =NORM.S.DIST(z,1) |
|||
| Confidence Intervals, Mean | |||
| Alpha = 1 - Confidence Level Margin of Error (E): E =CONFIDENCE.NORM(alpha,SD,n) |
|
||
|
E =CONFIDENCE.T(alpha,SD,n) (small sample) Confidence Interval: = (mean - E) to (mean + E) |
|||
| Confidence Intervals, Proportion | |||
|
z =NORM.S.INV(confidence level + alpha/2) E =z * SQRT(p * (1-p)/n) Confidence Interval: =(proportion - E) to =(prop + E) |
|||
| Sample Size, Mean | |||
|
z = NORM.S.INV(confidence+alpha/2) n = (z * SD / E)^2 |
|||
| Sample Size, Proportion | |||
| n = p*(1 − p) * (z / E)^2 | |||
| Hypothesis Testing Mean | |||
|
Decision Rule (D.R.): 1-tail: z =NORM.S.INV(confidence level) 2-tail: z =NORM.S.INV(confidence level + alpha/2) Test Statistic: z = (sample mean - mean) / (SD/SQRT(n)) |
|||
| Hypothesis Testing Proportion | |||
| Test Statistic: z = (ps - p) / SQRT(p*(1 - p) / n) | |||
| Hypothesis Testing Small Sample | |||
|
D.R.: 1 tail: t =T.INV(alpha,df) D.R.: 2 tail: t =T.INV.2T(alpha,df) Where df = degrees of freedom = n - 1 |
|
||
| Hypothesis Testing P-Value | |||
|
1-tail: z =NORM.DIST(sample mean, population mean, SD / SQRT(n),1) 2-tail: z = NORM.DIST(sample mean, population mean, SD / SQRT(n),1)*2 |
|||
| Regression Analysis | |||
| y = m*x + b (but in statistics it's written y = a + bx) | |||
| In Excel, "Multiple R" = coefficient of correlation | |||