RANK & statistical aggregates
Ranking, percentiles, leaderboards, weighted scores, z-scores, normalization — every statistical aggregate beyond AVERAGE.
26 pages · 3 reference, 23 how-to
Reference
RANK.EQ
Returns the rank of a number in a list, assigning the same rank to ties (same as RANK). Essential for leaderboards.
RANK.AVG
Returns the rank of a number, averaging ranks for ties. Useful in statistical analysis where tied ranks should not cluster.
PERCENTILE
Returns the k-th percentile value from a dataset. Useful for performance benchmarking and threshold analysis.
How-to guides
How to rank values
Rank a number against a list with RANK — highest first or lowest first. Works the same in Excel and Google Sheets.
How to calculate standard deviation
Measure how spread out your numbers are with STDEV for a sample or STDEVP for a whole population. Works in Excel and Google Sheets.
How to calculate a weighted average
Combine values and their weights with SUMPRODUCT divided by the total weight. Works the same in Excel and Google Sheets.
How to calculate standard deviation
Use STDEV.S for a sample or STDEV.P for a full population to measure the spread of values around the mean.
Excel ≠ SheetsHow to calculate variance
Use VAR.S for sample variance or VAR.P for population variance to quantify data dispersion in Excel or Google Sheets.
Excel ≠ SheetsHow to calculate a percentile
Use PERCENTILE.INC or PERCENTILE.EXC to find the value at a given percentile rank within a dataset.
Excel ≠ SheetsHow to rank values without ties
Use RANK combined with COUNTIF to break ties and assign a unique rank to every value in a list.
How to find the nth largest value
Use the LARGE function to return the nth highest value from a range — 1 for the maximum, 2 for the second largest, and so on.
How to calculate the range of a set of values
Subtract the MIN from the MAX of a dataset to calculate the statistical range in Excel or Google Sheets.
How to find outliers in a dataset
Use IQR-based bounds with QUARTILE to flag outliers in your data using an IF formula in Excel or Google Sheets.
How to find the most frequent text value
Use INDEX/MATCH with COUNTIF to find the most frequently occurring text value in Excel and Google Sheets.
How to calculate a weighted score
Use SUMPRODUCT to multiply scores by weights and sum the result to compute a weighted average or total score.
How to build a ranked leaderboard
Use RANK to assign positions and LARGE/INDEX/MATCH to sort a leaderboard by score in Excel or Google Sheets.
How to calculate a percentile rank
Use PERCENTRANK to find where a value falls within a dataset as a percentage in Excel or Google Sheets.
How to rank values within each group
Use COUNTIFS to build a within-group rank formula without needing helper columns in Excel or Google Sheets.
How to grade scores on a curve
Curve exam scores by adding a flat boost, scaling to a target average, or using a square-root curve in Excel and Google Sheets.
How to calculate a weighted GPA
Calculate a weighted GPA using SUMPRODUCT to multiply grade points by credit hours then divide by total credits.
How to find the second largest value
Use the LARGE function to retrieve the second (or Nth) largest value from a dataset in Excel and Google Sheets.
How to find the nth smallest value
Use the SMALL function to retrieve the 1st, 2nd, 3rd, or any nth smallest number from a range of values.
How to calculate a z-score
Compute a z-score (standard score) using AVERAGE and STDEV functions to measure how many standard deviations a value is from the mean.
How to normalize values to a 0-1 range
Apply min-max normalization with a formula using MIN and MAX to scale any dataset to a 0–1 range.
RANK vs RANK.EQ: legacy and modern ranking in Excel and Sheets
RANK and RANK.EQ produce identical results; RANK.EQ is the modern name retained for clarity. Learn the difference and when each applies in Excel and Google Sheets.
Excel ≠ SheetsHow to rank values with a tiebreaker
Assign unique ranks in Excel or Google Sheets when values tie — combine RANK with COUNTIFS on a secondary criterion to eliminate duplicate rank positions.