Percentile Calculations Compared Across Excel and SQL

Ask two tools for the 90th percentile of the same data and you can get two different numbers, both defensible. The reason is that the percentile of a finite sample has no single definition; statisticians list at least nine, and software picks among them. Excel offers two, PostgreSQL offers two, and they do not line up one to one. For large datasets the differences vanish into the noise. For a dozen response times in an SLA report, they can decide whether you met the target. This article lines up the functions, works the numbers on a five-value example and says how to choose. It is one of the worked pairs in moving between Excel and SQL, alongside rank customers by spend in Excel and SQL and finding the second highest value in Excel and in SQL.

Sample data

Five response times in milliseconds: 10, 20, 30, 40, 50, in A2:A6 and in a table timings(ms). With so few values, the arithmetic is easy to follow, and that is the point.

The two interpolated definitions

Both use a position in the sorted data and interpolate between neighbors. They differ in how they place that position.

**Inclusive (INC).** Position = (n − 1) × p + 1, where n is the number of values and p is the percentile as a fraction. This treats the minimum as the 0th percentile and the maximum as the 100th.

**Exclusive (EXC).** Position = (n + 1) × p. This treats the minimum and maximum as lying inside the 0th and 100th percentiles, so those extremes are not reachable.

For p = 0.9 and n = 5:

• Inclusive: position = 4 × 0.9 + 1 = 4.6. The 4th value is 40 and the 5th is 50, so the result is 40 + 0.6 × 10 = 46.

• Exclusive: position = 6 × 0.9 = 5.4. That is beyond the 5th value, which is the last one, so it cannot be computed. Excel's PERCENTILE.EXC returns #NUM!.

For p = 0.5:

• Inclusive: position = 4 × 0.5 + 1 = 3, which is the third value, 30.

• Exclusive: position = 6 × 0.5 = 3, also 30.

For p = 0.25:

• Inclusive: position = 4 × 0.25 + 1 = 2, the second value, 20.

• Exclusive: position = 6 × 0.25 = 1.5, halfway between 10 and 20, so 15.

The same data, two answers for the first quartile: 20 and 15.

Excel's functions

=PERCENTILE.INC(A2:A6, 0.9)      46
=PERCENTILE.EXC(A2:A6, 0.9)      #NUM!
=PERCENTILE.EXC(A2:A6, 0.25)     15
=QUARTILE.INC(A2:A6, 1)          20
=QUARTILE.EXC(A2:A6, 1)          15
=MEDIAN(A2:A6)                   30
=PERCENTRANK.INC(A2:A6, 46)      0.9

PERCENTILE and QUARTILE without a suffix are the older compatibility functions and equal the INC versions. PERCENTILE.EXC accepts p only from 1/(n+1) to n/(n+1): for n = 5 that is 0.1667 to 0.8333, so p = 0.9 fails. MEDIAN is the 50th percentile; with an even count it averages the two middle values.

Excel ignores blanks and text in the range. A range containing errors returns an error.

PostgreSQL's functions

PostgreSQL provides ordered-set aggregates:

SELECT
  percentile_cont(0.9) WITHIN GROUP (ORDER BY ms) AS p90_cont,
  percentile_disc(0.9) WITHIN GROUP (ORDER BY ms) AS p90_disc,
  percentile_cont(0.5) WITHIN GROUP (ORDER BY ms) AS median
FROM timings;

For the sample, percentile_cont(0.9) returns 46, the same as PERCENTILE.INC: it uses the inclusive position formula and interpolates linearly. percentile_disc(0.9) returns an actual value from the data. It chooses the first value in the sorted order whose cumulative distribution is at least p. The cumulative shares for 10, 20, 30, 40, 50 are 0.2, 0.4, 0.6, 0.8 and 1.0. The first to reach 0.9 is 50, so percentile_disc(0.9) is 50. For p = 0.5 it returns 30, since 0.6 is the first share at or above 0.5. For p = 0.25 it returns 20 (0.4 is the first share at least 0.25).

There is no EXC equivalent built in. If you need the exclusive definition, compute the position yourself or use a statistics extension.

Arrays of percentiles work: percentile_cont(ARRAY[0.5, 0.9, 0.99]) WITHIN GROUP (ORDER BY ms) returns an array. Grouped percentiles are GROUP BY as usual. NULLs are ignored. These are aggregate functions that need to sort the data, so they are heavier than avg; on huge tables, databases with approximate quantile sketches (approx_percentile, tdigest) trade accuracy for speed.

Side by side

For the sample data, with n = 5:

INC equals percentile_cont everywhere. EXC differs at the lower quartile and fails at the 90th. disc equals the data point at or above the interpolated answer. As n grows, all four converge: with 10,000 values, the differences are a fraction of the gap between neighboring points.

Which one to use

Match the audience's convention. A report compared with last year's must use last year's definition. Changing from EXC to INC moves the numbers without any change in the data.

**Use INC / percentile_cont as the default.** It is the most common, it is reachable for p from 0 to 1 and it agrees across Excel, PostgreSQL, NumPy's default (linear), pandas' quantile and R's type 7. If your tools are Excel, SQL and Python, they will agree.

**Use percentile_disc when the answer must be an observed value.** "The 95th percentile response time was 1,230 ms" is a statement about a real request when computed with disc, while an interpolated figure may correspond to no request. For latency SLAs, many teams use the nearest-rank method, which is disc.

**Use EXC when a statistical method calls for it**, such as certain textbook procedures for small samples. It is rarely the right default.

NumPy and pandas offer a method parameter: numpy.percentile(x, 90, method="linear") is INC, and method="weibull" is EXC. Check the default when comparing across tools.

Small samples and ties

On small samples, percentiles are jumpy. With n = 5, the 90th percentile is determined by two points. Do not report a 99th percentile of 20 values: it is the maximum with a flourish. A rule of thumb is that you need at least about 1/(1−p) values to say anything about the pth percentile, so 100 values for the 99th. State n beside the percentile.

Ties do not break the formulas; repeated values occupy several positions. percentile_disc is stable in the presence of ties, and interpolation between two equal values returns that value.

NULLs, blanks and outliers

Both tools ignore blank cells and NULLs, which shrinks n. If a blank means "no measurement" that is right; if it means zero, the percentile is overstated. Decide and fill accordingly. Outliers affect high percentiles more than the median, which is the reason for using percentiles instead of means in the first place.

Checking

Use the five-value example as a test: any implementation should give 20, 30 and 46 for the three percentiles under the inclusive definition. Run your sheet and your query on it before running them on real data. Then compare them on the real data and expect agreement to the last digit for INC against percentile_cont. If they differ, check for blank or NULL handling, filters that differ and duplicates.

When percentile definitions do not matter

If your data has thousands of values, any definition gives the same figure to the precision you will report, and you can pick the default. If the question is "what share of requests are slower than 500 ms", a count and a division answers it exactly, with no percentile definition at all: count(*) FILTER (WHERE ms > 500) / count(*). And when the audience wants a threshold, a service target for example, state it as the share ("99% of requests within 500 ms"), which is the same statement without the ambiguity. Microsoft documents PERCENTILE.INC and PERCENTILE.EXC, and the PostgreSQL manual describes ordered-set aggregates. For other statistics translated between the tools, see translating Excel formulas to SQL.