What's the difference between PERCENTRANK.INC and PERCENTRANK.EXC?

INC (inclusive) produces outputs from 0 to 1 (min and max included); EXC (exclusive) produces values strictly between 0 and 1. Use INC for most business rankings. Use EXC if you need statistical percentiles that exclude extremes.

How do I find the value at a given percentile instead of the percentile of a value?

Use PERCENTILE.INC instead—it's the inverse function. Example: =PERCENTILE.INC(array,0.67) returns the value at the 67th percentile, whereas PERCENTRANK.INC returns the percentile for a given value.

Can I use PERCENTRANK.INC with dates?

Yes—Excel stores dates as numbers internally, so =PERCENTRANK.INC(date_range, specific_date) works correctly. Text values cause #VALUE! errors; use COUNTIF or SUMPRODUCT to rank text instead.

What does the significance parameter actually do in practice?

It controls the number of significant figures returned. Significance=3 (default) outputs 0.667; significance=2 outputs 0.67. It only affects display rounding, not the calculation; it's useful for reports but doesn't change ranking logic.

PERCENTRANK.INC function

Returns the percentile rank of a value within a dataset as a decimal from 0 to 1, showing where it stands relative to all other values.

=PERCENTRANK.INC(array, x, [significance])

Generate a PERCENTRANK.INC formula

Describe what you need. The generator will reach for PERCENTRANK.INC where PERCENTRANK.INC is the right tool, and tell you when it is not.

Arguments

How PERCENTRANK.INC reads its arguments

Argument	Required	Description
array	Required	A range or array of numeric values; must contain at least two distinct values to calculate a meaningful percentile rank.
x	Required	The value whose percentile rank you want to find; can be a number inside or outside the array, but must be numeric.
significance	Optional	Optional; the number of significant digits to round the result to (default is 3); must be a positive integer.

Returns

A decimal number between 0 and 1 (inclusive) representing the value's percentile position in the dataset.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Find where a customer's MRR ranks among all customers

Customer	MRR	Signup	Plan
Acme Corp	2500	1/15/2024	Pro
Beta Inc	499	6/20/2023	Starter
CloudFirst	5000	3/10/2023	Enterprise
DataCo	2000	2/1/2024	Pro
EdgeTech	500	5/12/2024	Starter
FutureSoft	2200	11/30/2023	Pro
GrowthLab	4800	4/18/2024	Enterprise

=PERCENTRANK.INC($B$2:$B$8,B2)

Result: 0.666667

Acme's $2,500 MRR is the 5th value in the sorted list ($499, $500, $2,000, $2,200, $2,500, $4,800, $5,000). The formula calculates (5−1)÷(7−1) = 0.667, placing Acme in the upper two-thirds of customers by revenue.

2. Identify the highest-value customer by percentile rank

Customer	MRR	Signup	Plan
Acme Corp	2500	1/15/2024	Pro
Beta Inc	499	6/20/2023	Starter
CloudFirst	5000	3/10/2023	Enterprise
DataCo	2000	2/1/2024	Pro
EdgeTech	500	5/12/2024	Starter
FutureSoft	2200	11/30/2023	Pro
GrowthLab	4800	4/18/2024	Enterprise

=PERCENTRANK.INC($B$2:$B$8,B4)

Result: 1

CloudFirst's $5,000 MRR is the maximum value, so it ranks at 100% (1.0). The formula (7−1)÷(7−1) always returns 1 for the largest value in any dataset.

3. Compare a low-revenue customer with limited decimal precision

Customer	MRR	Signup	Plan
Acme Corp	2500	1/15/2024	Pro
Beta Inc	499	6/20/2023	Starter
CloudFirst	5000	3/10/2023	Enterprise
DataCo	2000	2/1/2024	Pro
EdgeTech	500	5/12/2024	Starter
FutureSoft	2200	11/30/2023	Pro
GrowthLab	4800	4/18/2024	Enterprise

=PERCENTRANK.INC($B$2:$B$8,B3,2)

Result: 0

Beta's $499 MRR is the minimum value, returning 0 for the 0th percentile (1−1)÷(7−1). The significance parameter set to 2 limits output to 2 significant digits, which is why this displays as 0 rather than 0.00.

Common errors

Which PERCENTRANK.INC error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The array contains text, logical values, or other non-numeric data; PERCENTRANK.INC cannot rank text or mixed data types.	Ensure the array contains only numeric values; remove text entries like "N/A" or "TBD" from the range before using the function.
#NUM!	The array contains only one unique value (all values are identical) or significance parameter is invalid (0, negative, or non-integer).	Include at least two distinct values in the array; also verify significance is a positive integer like 1, 2, or 3.
#REF!	The array range references cells that were deleted, or the reference syntax is malformed (e.g., missing colon in range notation).	Verify the range exists and uses correct syntax like $B$2:$B$8; update the formula if rows or columns were removed.

Tips and when to use something else

Use LARGE or SMALL to find the actual value at a specific percentile—these are the reverse of PERCENTRANK.INC.
Combine with ROUND() for cleaner output: =ROUND(PERCENTRANK.INC(...),2) returns exactly 2 decimal places instead of 15.
When data grows, use dynamic ranges with INDEX: =PERCENTRANK.INC(INDEX($B:$B,2,1):INDEX($B:$B,COUNTA($B:$B),1),x) to avoid manual updates.
PERCENTRANK.INC is inclusive (0 and 1 are valid); to manually calculate exclusive percentiles, use =(COUNTIF(range,"<"&x))/(COUNTA(range)−1).

Related functions

LARGELARGE returns the k-th largest value in an array, where k=1 is the maximum, k=2 is the second-largest, and so on.SMALLSMALL returns the k-th smallest value from an array, where k=1 gives the minimum and higher k values give progressively larger results.COUNTIFCounts cells in a range that meet a specific criterion, returning a number — use it to tally matching rows like employee counts by department.MAXMAX returns the largest numeric value in a range, ignoring text and empty cells. Useful for finding peak inventory, highest prices, or latest dates.MINMIN finds and returns the smallest numeric value in a range or list, automatically excluding text and empty cells.

Frequently asked questions

What's the difference between PERCENTRANK.INC and PERCENTRANK.EXC?: INC (inclusive) produces outputs from 0 to 1 (min and max included); EXC (exclusive) produces values strictly between 0 and 1. Use INC for most business rankings. Use EXC if you need statistical percentiles that exclude extremes.
How do I find the value at a given percentile instead of the percentile of a value?: Use PERCENTILE.INC instead—it's the inverse function. Example: =PERCENTILE.INC(array,0.67) returns the value at the 67th percentile, whereas PERCENTRANK.INC returns the percentile for a given value.
Can I use PERCENTRANK.INC with dates?: Yes—Excel stores dates as numbers internally, so =PERCENTRANK.INC(date_range, specific_date) works correctly. Text values cause #VALUE! errors; use COUNTIF or SUMPRODUCT to rank text instead.
What does the significance parameter actually do in practice?: It controls the number of significant figures returned. Significance=3 (default) outputs 0.667; significance=2 outputs 0.67. It only affects display rounding, not the calculation; it's useful for reports but doesn't change ranking logic.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17