Documentation/Calc Functions/RANK

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Calc Functions Home | List of functions | Abbreviations

Function name:

RANK

Category:

Statistical Analysis

Summary:

Calculates the rank of a given value within a supplied list of numbers. If you sort the list, a number's rank is its position within the sorted list.

If there are repeated numeric values in the list, the "closer-to-first" rank for the same value is returned.

Syntax:

RANK(Value; Data[; Type])

Returns:

Returns a positive (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.

If a number in Data occurs more than once, it is given the same rank, but it also increments the rank for subsequent different numbers.

If Value does not exist in the range of Data, then the function returns an error.

If Value is not a real number (nor a reference to a real number), then the function returns an error.

Arguments:

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

Data is an 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 (optional) sequence order. The intended values for Type are as follows:

Type = 0 (zero, default). Data is ranked in Descending order; from the greatest item of the array to the smallest. The largest/greatest value in Data gets the first rank (1). The smallest/least value in Data gets the last (greatest number) rank.
Type = 1. Data is ranked in Ascending order; from the smallest/least item of the array to the greatest. The smallest value in Data gets the first rank (1). The largest/greatest value in Data gets the last (greatest number) rank.

If a number in Data occurs more than once, it is given the "closer-to-first" rank of all the same numbers.

If Data includes values that are not real numbers (e.g. "string"), then the function ignores such values.

If Type is either TRUE or any non-zero real number, then Type is treated as 1.

If Type is FALSE, then Type is treated as 0 (zero).

Additional details:

Note:
The difference between RANK.AVG and RANK.EQ/RANK occurs when there are repeated numeric values in Data. The RANK.EQ/RANK function returns the same "closer-to-first" rank, whereas the RANK.AVG function returns the average rank for all the same repeated values. This means that RANK.EQ/RANK returns positive integer numbers for repeated numeric values in Data, whereas RANK.AVG may return positive non-integer numbers for such repeated numeric values in Data.

Warning:
Since LibreOffice 4.0 and until 7.5.2 (included), the function had an implementation error. For numeric values that are not listed within Data, the function returned numeric values, which was incorrect. Since LibreOffice 7.5.3, the implementation is correct, not only for values listed within Data. The "Examples" section includes results for the correct implementation, with a mark for cases in which an incorrect result would be obtained with the prior incorrect implementation.

Examples:

	A	B	C	D	E	F	G
1	Data	RANK( An; $A$2:$A$9; 0 )	RANK( An; $A$2:$A$9; 1 )	RANK( An; $A$2:$A$12; 0 )	RANK( An; $A$2:$A$12; 1 )	RANK.AVG( An; $A$2:$A$12; 0 )	RANK.AVG( An; $A$2:$A$12; 1 )
2	15	3	4	5	4	5.5	4.5
3	17	2	5	2	6	3	7
4	14.8	4	3	7	3	7	3
5	−2	6	1	9	1	9	1
6	19	1	6	1	9	1	9
7	13.3	5	2	8	2	8	2
8	string	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!	#VALUE!
9	⁠	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
10	17	2	5	2	6	3	7
11	15	3	4	5	4	5.5	4.5
12	17	2	5	2	6	3	7
13	⁠	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
14	−10	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
15	20	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
16	4	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A

Formula	Description	Returns
=RANK( -2; $A$2:$A$9; 0)	Rank of `-2` in the given Data's range in descending order.	6
=RANK( -2; $A$2:$A$9; 1)	Rank of `-2` in the given Data's range in ascending order.	1
=RANK( 19; $A$2:$A$9)	Rank of 19 in the given Data's range in (default) descending order.	1
=RANK( 19; $A$2:$A$9; 1)	Rank of 19 in the given Data's range in ascending order.	6
=RANK( -2; $A$2:$A$12; 0)	Rank of `-2` in the given Data's range (which includes duplicates) in descending order.	9
=RANK( 19; $A$2:$A$12; 1)	Rank of 19 in the given Data's range (which includes duplicates) in ascending order.	9
=RANK( 17; $A$2:$A$12; 0)	Rank of 17 in the given Data's range (which includes duplicates) in descending order.	2
=RANK( 15; $A$2:$A$12; 0)	Rank of 15 in the given Data's range (which includes duplicates) in descending order.	5
=RANK( 14.8; $A$2:$A$12; 0)	Rank of 14.8 in the given Data's range (which includes duplicates) in descending order.	7
=RANK( 17; $A$2:$A$12; 1)	Rank of 17 in the given Data's range (which includes duplicates) in ascending order.	6
=RANK( 15; $A$2:$A$12; 1)	Rank of 15 in the given Data's range (which includes duplicates) in ascending order.	4
=RANK( -10; $A$2:$A$9)	`-10` is outside the range of values in Data.	#N/A
=RANK( 20; $A$2:$A$9)	20 is outside the range of values in Data.	#N/A
=RANK( 4; $A$2:$A$9; 1)	The value `4` falls within the range of values (of Data) but it is not listed in Data.	#N/A
=RANK( 4; $A$2:$A$9)	The value `4` falls within the range of values (of Data) but it is not listed in Data.	#N/A

Related LibreOffice functions:

ODF standard:

Section 6.18.65, part 2

Related (or similar) Excel functions:

RANK