Wauvel

Excel like a finance pro.

← The library

Statistical function

PERCENTRANK.EXC

Returns the rank of a value as a percentage of the data set, excluding 0 and 1.

Rarely usedDifficulty 1350 · AdvancedUsage rank #328 of 520
Practice · 2 questions →

When to use it

The rank of a value as a fraction of the data set using the exclusive method: positions run from 1/(n+1) to n/(n+1).

The shape of it

Syntax
=PERCENTRANK.EXC(array, x, [significance])

Worked examples

  • A value in the set

    =PERCENTRANK.EXC({1,2,3,4,5},4) → 0.666

    4 is at position 4 of 6.

  • Minimum

    =PERCENTRANK.EXC({1,2,3,4,5},1) → 0.166

    The minimum is not 0 under this method.

  • Interpolated

    =PERCENTRANK.EXC({1,2,3,4,5},2.5) → 0.416

    Interpolated between 2 and 3.

Worth knowing

  • Matches PERCENTILE.EXC.
  • Default 3 significant digits; pass a larger significance for more.
  • Values outside the data return #N/A.

Where it goes wrong

  • #N/A when x is outside the data.
  • Differs from PERCENTRANK.INC at the ends.

Related

AVERAGECOUNTCOUNTACOUNTIFCOUNTIFSMAX

Learn the moves here — or let Wauvel run them on your numbers.

Meet your AI CFO →

One CFO-grade Excel tip a week

A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.