Excel Cheat Sheet
Part 1 – Descriptive statistics
CONCEPT EXCEL FORMULA
Mean (arithmetic) =AVERAGE(range)
Harmonic Mean =HARMEAN(range)
Geometric Mean =GEOMEAN(range)
Median =MEDIAN(range)
Mode =[Link](range)
Lower Quartile(Q1) =[Link](range, 1)
Upper Quartile(Q3) =[Link](range, 3)
Percentile(any) =[Link](range, k)
Decile =[Link](range, 0.1) – for 1st decile
Minimum =MIN(range)
Maximum =MAX(range)
Range =MAX(range) – MIN(range)
Variance (sample) =VAR.S(range)
Variance (population) =VAR.P(range)
Std Deviation (sample) =STDEV.S(range)
Std Deviation (Population) =STDEV.P(range)
Coefficient of Variation =STDEV.S(range) / AVERAGE(range)
Skewness =SKEW(range)
Kurtosis =KURT(range)
Standard error of mean =STDEV.S(range)/SQRT(COUNT(range))
Z-Score =STANDARDIZE(x, mean, stdev)
Part 2 – Frequency Distribution
Frequency Table =FREQUENCY (data_array, bins_array)
Relative Frequency =Frequency / SUM(frequencies)
Cumulative Frequency =SUM(frequencies_from_start) or =COUNTIF
(data, "<="&value)
Cumulative Rel Frequency =cumulative_freq/SUM(frequencies)
Number of classes (sturges) =1+3.3*LOG10(COUNT(range))
Class Width =(MAX(range) – MIN(range)) / k
Class Midpoint ={lower + upper) / 2
Part 3 – Decomposition of Variability
Weighted Mean =SUMPRODUCT(weights, values) /
SUM(weights)
Within-group Variability =SUMPRODUCT(variance, n) / SUM(n)
Between group Variability =SUMPRODUCT((means-total_mean)^2,
n)/SUM(n)
Total Variability =Within + Between
Part 4 – Probability Basics
Classical Probability =favorable/total
Statistical Probability =count_of_event/n_trials
Complement of Events =1 – P(A)
Union of Events =P(A) + P(B) - P(A∩B)
Intersection (independent) =P(A)*P(B)
Intersection (dependent) =P(A)*P(B|A)
Conditional Probability =P(A∩B)/P(B)
Mutually Exclusive If A∩B = 0, then P(A∪B)=P(A)+P(B)
Part 5 – Discrete Probability Distributions
Bernoulli (alternative) =[Link](1, 1, p, FALSE) for success
Binomial PDF =[Link](k, n, p, FALSE)
Binomial CDF =[Link](k, n, p, TRUE)
Hypergeometric (dependent) =[Link](k, n, K, N, FALSE)
Poisson PDF =[Link](x, mean, FALSE)
Poisson CDF =[Link](x, mean, TRUE)
Part 6 – Random Variable Characteristics
Expected Value (discrete) =SUMPRODUCT(x_values, probabilities)
Variance (discrete) =SUMPRODUCT((x_values-mean)^2,
probabilities)
Expected value (continuous) Requires integration or discrete approx.
Variance (continuous) Requires integration or discrete approx.
Standard Deviation =SQRT(variance)
Part 7 – Quantiles from Probability Distributions
Median from distribution Find x where F(x) = 0.5
Lower quartile from distribution Find x where F(x) = 0.25
95% quantile from distribution Find x where F(x) = 0.95
PART 1: DESCRIPTIVE STATISTICS
Task Excel Formula
Count values =COUNT(range)
Sum =SUM(range)
Mean =AVERAGE(range)
Median =MEDIAN(range)
Mode (single) =[Link](range)
Mode (multiple) =[Link](range)
Task Excel Formula
Minimum =MIN(range)
Maximum =MAX(range)
Range =MAX(range)-MIN(range)
Lower quartile (Q1) =[Link](range, 1)
Upper quartile (Q3) =[Link](range, 3)
Percentile =[Link](range, k)
Decile (10th) =[Link](range, 0.1)
Sample variance =VAR.S(range)
Population variance =VAR.P(range)
Sample std deviation =STDEV.S(range)
Population std deviation =STDEV.P(range)
Coefficient of variation =STDEV.S(range)/AVERAGE(range)
Skewness =SKEW(range)
Kurtosis =KURT(range)
Standard error of mean =STDEV.S(range)/SQRT(COUNT(range))
Z-score =STANDARDIZE(x, mean, stdev)
PART 2: FREQUENCY DISTRIBUTION
Task Excel Formula
Number of classes (Sturges) =1+3.3*LOG10(COUNT(range))
Class width =(MAX(range)-MIN(range))/k
Frequency table =FREQUENCY(data_array, bins_array)
Count if condition =COUNTIF(range, criteria)
Count if multiple conditions =COUNTIFS(range1, criteria1, range2, criteria2)
Relative frequency =frequency/SUM(frequencies)
Cumulative frequency =SUM(frequencies_from_start) or =COUNTIF(data, "<="&value)
Cumulative relative frequency =cumulative_freq/SUM(frequencies)
Class midpoint =(lower+upper)/2
PART 3: GROUPED DATA (DECOMPOSITION OF VARIABILITY)
Task Excel Formula
Weighted mean =SUMPRODUCT(weights, values)/SUM(weights)
Within-group variability =SUMPRODUCT(variances, n)/SUM(n)
Between-group variability =SUMPRODUCT((means-total_mean)^2, n)/SUM(n)
Total variability =Within + Between
PART 4: PROBABILITY BASICS
Task Excel Formula
Classical probability =favorable/total
Task Excel Formula
Statistical probability =count_of_event/n_trials
Complement =1-P(A)
Union (general) =P(A)+P(B)-P(A∩B)
Union (disjoint) =P(A)+P(B)
Intersection (independent) =P(A)*P(B)
Intersection (dependent) =P(A)*P(B|A)
Conditional probability =P(A∩B)/P(B)
Combinations =COMBIN(n, k)
Permutations =PERMUT(n, k)
PART 5: DISCRETE DISTRIBUTIONS
Binomial Distribution
What Excel Formula
P(X = k) =[Link](k, n, p, FALSE)
P(X ≤ k) =[Link](k, n, p, TRUE)
Expected value =n*p
Variance =n*p*(1-p)
Standard deviation =SQRT(n*p*(1-p))
Poisson Distribution
What Excel Formula
P(X = k) =[Link](k, λ, FALSE)
P(X ≤ k) =[Link](k, λ, TRUE)
Expected value =λ
Variance =λ
Standard deviation =SQRT(λ)
Hypergeometric Distribution
What Excel Formula
P(X = k) =[Link](k, n, M, N, FALSE)
P(X ≤ k) Not direct (sum individual)
Expected value =n*M/N
Variance =n*(M/N)*(1-M/N)*((N-n)/(N-1))
Bernoulli Distribution (n=1)
What Excel Formula
P(X = 1) =[Link](1, 1, p, FALSE)
P(X = 0) =[Link](0, 1, p, FALSE)
Expected value =p
Variance =p*(1-p)
PART 6: RANDOM VARIABLE CHARACTERISTICS
Task Excel Formula
Expected value (discrete) =SUMPRODUCT(x_values, probabilities)
Variance (discrete) =SUMPRODUCT((x_values-mean)^2, probabilities)
Standard deviation (discrete) =SQRT(variance)
Median from distribution Find x where F(x)=0.5
Quantile from distribution Find x where F(x)=p
PART 7: NORMAL DISTRIBUTION
Task Excel Formula
P(X ≤ x) =[Link](x, μ, σ, TRUE)
P(X = x) =[Link](x, μ, σ, FALSE)
x for given p (quantile) =[Link](p, μ, σ)
P(Z ≤ z) =[Link](z, TRUE)
z for given p =[Link](p)
z-score =(x-μ)/σ or =STANDARDIZE(x, μ, σ)
PART 8: CONFIDENCE INTERVALS
Mean (σ known)
Bound Formula
Lower =x̄ - [Link](1-α/2)*σ/SQRT(n)
Upper =x̄ + [Link](1-α/2)*σ/SQRT(n)
Mean (σ unknown)
Bound Formula
Lower =x̄ - [Link].2T(α, n-1)*s/SQRT(n)
Upper =x̄ + [Link].2T(α, n-1)*s/SQRT(n)
Proportion
Bound Formula
Lower =p - [Link](1-α/2)*SQRT(p*(1-p)/n)
Upper =p + [Link](1-α/2)*SQRT(p*(1-p)/n)
Margin of Error (MOE)
Type Formula
Mean (σ known) =[Link](1-α/2)*σ/SQRT(n)
Mean (σ unknown) =[Link].2T(α, n-1)*s/SQRT(n)
Proportion =[Link](1-α/2)*SQRT(p*(1-p)/n)
Standard Error
Type Formula
Mean =σ/SQRT(n) or =s/SQRT(n)
Proportion =SQRT(p*(1-p)/n)
PART 9: HYPOTHESIS TESTING
Test Statistics
Test Formula
Mean (σ known) z =(x̄ - μ0)/(σ/SQRT(n))
Mean (σ unknown) t =(x̄ - μ0)/(s/SQRT(n))
Test Formula
Proportion z =(p - π0)/SQRT(π0*(1-π0)/n)
Critical Values
Distribution Formula
z (right tail) =[Link](1-α)
z (two-tailed) =[Link](1-α/2)
t (right tail) =[Link](1-α, df)
t (two-tailed) =[Link].2T(α, df)
p-values
Test Type Formula
z (left-tailed) =[Link](z, TRUE)
z (right-tailed) =[Link](z, TRUE)
z (two-tailed) =2*([Link](ABS(z), TRUE))
t (left-tailed) =[Link](t, df, TRUE)
t (right-tailed) =[Link](t, df, TRUE)
t (two-tailed) =[Link].2T(ABS(t), df)
PART 10: DISTRIBUTION QUANTILES (TABLES)
Distribution Left Tail Right Tail
Chi-square (χ²) =[Link](p, df) =[Link](1-p, df)
t (Student) =[Link](p, df) =[Link](1-p, df) or =[Link].2T(2*(1-p), df)
Distribution Left Tail Right Tail
F (Fisher-Snedecor) =[Link](p, v1, v2) =[Link](1-p, v1, v2)
PART 11: COMMON VALUES (α and Confidence)
Confidenc 1- [Link]
α
e α/2 V
0.1
90% 0.95 1.645
0
0.0 0.97
95% 1.96
5 5
0.0 0.99
99% 2.576
1 5
α (two-tailed) [Link].2T(α, df)
0.10 1.645 (large df)
0.05 1.96 (large df)
0.01 2.576 (large df)