Documentation/Calc Functions/RANK

From The Document Foundation Wiki
Jump to navigation Jump to search


Function name:

RANK

Category:

Statistical Analysis

Summary:

Calculates the rank of a given value within a supplied list of numbers. The rank of a number is its size relative to other values in the list; this means that if you sort the list, a number’s rank is its position within the sorted list.

Syntax:

RANK(Value; Data[; Type])

Returns:

Returns an integer value which is the rank of the Value in the given Data with given sequence order Type. It ranges from 1 to the number of elements in Data.

Arguments:

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

Data is the array or a reference to a range of cells containing the list of real numbers from which Value's rank is to be determined.

Type is the sequence order. Type = 0 means descending from the last item of the array to the first (this is the default), Type = 1 means ascending from the first item of the array to the last.

  • If a number in Data occurs more than once it is given the same rank, but increments the rank for subsequent different numbers.
  • If Value does not exist in the range of Data then the function returns a value (#VALUE!) error.
  • If Type is any real number other than 0 or 1 then the function returns -1 for valid values of other arguments.
  • If Data contains a string (passed in double-quotes in an array and directly when passed as reference) then the function returns -1 else for all non-real numbers, the function returns an error value.

Additional details:

Note pin.svg

The difference between RANK.AVG and RANK.EQ/RANK occurs when there are duplicates in the list of values. The RANK.EQ/RANK function returns the lower rank, whereas the RANK.AVG function returns the average rank.


Examples:

Formula Description Returns
=RANK(3;A1:A5;0) where A1:A5 contains 5,7,3,2,9 Returns the rank of 3 in the given Data's range in descending order. 4
=RANK(3;A1:A5;1) where A1:A5 contains 5,7,3,2,9 Returns the rank of 3 in the given Data's range in ascending order. 2
=RANK(2;A1:A5) where A1:A5 contains 5,7,3,2,9 Returns the rank of 2 in the given Data's range in descending order (default value of Type is 0). 5
=RANK(1;A1:A5) where A1:A5 contains 5,7,3,2,9 The function returns an error since 1 is outside the range of Data. #VALUE!
=RANK(4;A1:A5) where A1:A5 contains 5,7,3,2,9 Returns the rank of 4 (not availabe in Data) when Data is arranged in descending order (by default). 4
=RANK(5;A1:A7;0) where A1:A7 contains 5,7,7,3,5,2,9 Returns the rank of 5 in the given Data's range in descending order. 4

Related LibreOffice functions:

RANK.AVG

RANK.EQ

ODF standard:

Section 6.18.65, part 2

Equivalent Excel functions:

RANK