Day 18: Statistical Functions for Analysis

PERCENTILE(array, k) returns the value at the k-th percentile of a dataset. k is between 0 and 1. =PERCENTILE(Revenue, 0.9) returns the revenue value at the 90th percentile — 90% of values are below this. Common uses: finding cutoff values for top 10% customers, identifying thresholds for outlier detection, understanding distribution shape. PERCENTILE.INC includes endpoints; PERCENTILE.EXC excludes them — for most analyst uses, PERCENTILE.INC is appropriate.

QUARTILE(array, quart) returns specific percentiles: QUARTILE(data,0) = minimum, QUARTILE(data,1) = 25th percentile (Q1), QUARTILE(data,2) = median (Q2, 50th percentile), QUARTILE(data,3) = 75th percentile (Q3), QUARTILE(data,4) = maximum. Interquartile Range (IQR) = Q3 - Q1, used for outlier detection: values below Q1 - 1.5×IQR or above Q3 + 1.5×IQR are statistical outliers.

RANK.EQ(number, ref, [order]) returns the rank of a number within a list. Order 0 = descending (largest = rank 1), Order 1 = ascending (smallest = rank 1). If there are tied values, both receive the same rank and the next rank is skipped. RANK.AVG gives tied values the average of the ranks they would occupy. Use RANK.EQ for leaderboard-style rankings (sales rep rankings, customer rankings) and RANK.AVG for statistical analysis where tied ranks matter.

CORREL(array1, array2) calculates the Pearson correlation coefficient between two datasets. Returns a value between -1 and 1. 1 = perfect positive correlation (as one increases, the other always increases). -1 = perfect negative correlation. 0 = no linear relationship. In data analysis: =CORREL(Marketing_Spend, Revenue) tells you how strongly marketing spend correlates with revenue. Important: correlation does not imply causation. Also: CORREL only detects linear relationships — a non-linear relationship may still exist even if CORREL is near 0.

STDEV.S calculates standard deviation for a sample (divides by n-1). STDEV.P calculates for a population (divides by n). In data analysis, use STDEV.S when your data is a sample from a larger population (which is almost always the case — you have transaction data from a period, not all transactions ever). Use STDEV.P only when you have the complete population. Standard deviation measures spread — a high StDev means high variability in the data.