Documentation/Calc Functions/PERCENTRANK.EXC

From The Document Foundation Wiki
Jump to navigation Jump to search


Function name:

PERCENTRANK.EXC

Category:

Statistical Analysis

Summary:

Calculates the percentage rank of a value in a data set. The percentage rank of a value is the relative position of that value in a data set, expressed as a percentage between 0 and 1 (both exclusive). In other words, it is the percentage of values in the data set that are equal to or less than itself.

Syntax:

PERCENTRANK.EXC(Data; Value[; Significance])

Returns:

Returns a real number between 0 and 1 (both exclusive) to the specified precision, which is the percentage rank of the specified value in the specified data set.

Arguments:

Data is an array or a reference to the range of cells that represents the array of data.

Value is a real number or a reference to the cell containing that number which represents the value whose percentile rank must be determined.

Significance is an integer value or a reference to the cell containing that number which specifies the number of significant digits that the returned percentage value is rounded to. If omitted, a value of 3 is used by default.

  • If the Value is not available in the Data then the function interpolates to give the correct percentage rank. However, if the Value falls outside the range of Data then the function returns a value (#VALUE!) error.
  • If Significance is not an integer value then the function uses the floor of that value, i.e. it is truncated to an integer. However, if the value is less than 1 then the function returns an error value.

Additional details:

  • Unlike PERCENTRANK.INC, which returns 1 when the largest value from the range of data is passed as Value, the PERCENTRANK.EXC will return a value between 0 and 1. Similarly, when the smallest value is passed it will return between 0 and 1 instead of 0.
  • For more details on percentage rank, visit Wikiepdia.
Note pin.svg

The difference between PERCENTRANK.INC/PERCENTRANK and PERCENTRANK.EXC is that PERCENTRANK.INC calculates a value in the range 0 to 1 inclusive, whereas the PERCENTRANK.EXC function calculates a value in the range 0 to 1 exclusive.


Examples:

Formula Description Returns
=PERCENTRANK.EXC(A1:A5;5;4) where A1:A5 contains 5,7,3,2,9 The function calculates the relative position of a value in a data set in A1:A5. 0.5
=PERCENTRANK.EXC(A1:A5;6;5) where A1:A5 contains 5,7,3,2,9 The function calculates interpolated value to give the correct relative position that value in the data set in A1:A5 since 6 is not available in the dataset. 0.58333
=PERCENTRANK.EXC(A1:A5;9;4) where A1:A5 contains 5,7,3,2,9 The function calculates the rank of a value in a data set Data as a percentage of the data set in A1:A5. Here, although 9 is the largest number in the range of dataset, the function doesn't return 1. 0.83333
=PERCENTRANK.EXC(A1:A5;11;2) where A1:A5 contains 5,7,3,2,9 The function returns a value error since 11 falls outside the total range of the data set in A1:A5. #VALUE!

Related LibreOffice functions:

PERCENTILE.EXC

PERCENTRANK

PERCENTRANK.INC

RANK

ODF standard:

None

Equivalent Excel functions:

PERCENTRANK.EXC