xlpRANK

Returns the rank of a number, according to size, in a cell range, with up to 32,767 significant digits of precision. Same as Excel's built-in RANK function, but with high precision.

If two or more numbers in numlist match num, both numbers have the same rank, and subsequent rankings are skipped according to how many numbers are tied. One fewer rankings are skipped than the number of matches -- if two numbers in numlist match num, one subsequent ranking is skipped; if three numbers match, two rankings are skipped, etc. For example:

Formula

Description

Result

=xlpRANK(A2,A1:C5)

	A	B	C
1	10	20	70
2	120	30	110
3	90	60	ABC
4	40		150
5	80	140	100

The rank of the number in cell A2 among the numbers in A1:C5.

=xlpRANK(A2,A1:C5,TRUE)

	A	B	C
1	10	20	70
2	120	30	110
3	90	60	ABC
4	40		150
5	80	140	100

The rank of the number in cell A2 among the numbers in A1:C5, ranked according to ascending order.

=xlpRANK(C2,A1:C5)

	A	B	C
1	10	-20	70
2	120	0	110
3	50	60	ABC
4	40		ABC
5	40	40	110

The rank of the number in cell C2 among the numbers in A1:C5.

=xlpRANK(C2,A1:C5,,TRUE)

	A	B	C
1	10	-20	70
2	120	0	110
3	50	60	ABC
4	40		ABC
5	40	40	110

The rank of the number in cell C2 among the numbers in A1:C5, with tie compensation.

2.5

=xlpRANK(A5,A1:C5)

	A	B	C
1	10	-20	70
2	120	0	110
3	50	60	ABC
4	40		ABC
5	40	40	110

The rank of the number in cell A5 among the numbers in A1:C5.

=xlpRANK(A5,A1:C5,,TRUE)

	A	B	C
1	10	-20	70
2	120	0	110
3	50	60	ABC
4	40		ABC
5	40	40	110

The rank of the number in cell A5 among the numbers in A1:C5, with tie compensation.

=xlpRANK(A16,A1:A17)

	A
1	100000000000000000000.1
2	1000000000000000000000.1
3	10000000000000000000000.1
4	100000000000000000000.01
5	1000000000000000000000.01
6	100000000000000000000.001
7	1000000000000000000000.001
8	100000000000000000000.0001
9	1000000000000000000000.0001
10	$1,000
11	5
12	$1,234,567,890,123,456,789.012345
13	1234567890123456789.012345
14	$100000000000000000000
15	$1000
16	1,234,567,890,123,456,789.012345
17	1.00000000000000000000000000001

The rank of the number in cell A16 among the numbers in A1:A17.

=xlpRANK(A16,A1:A17,,TRUE)

	A
1	100000000000000000000.1
2	1000000000000000000000.1
3	10000000000000000000000.1
4	100000000000000000000.01
5	1000000000000000000000.01
6	100000000000000000000.001
7	1000000000000000000000.001
8	100000000000000000000.0001
9	1000000000000000000000.0001
10	$1,000
11	5
12	$1,234,567,890,123,456,789.012345
13	1234567890123456789.012345
14	$100000000000000000000
15	$1000
16	1,234,567,890,123,456,789.012345
17	1.00000000000000000000000000001

The rank of the number in cell A16 among the numbers in A1:A17, with tie compensation.

num	Required. The number in numlist for which to return its rank.
numlist	Required. The range of cells with numbers.
ascending	Optional. Determines whether to rank num according to ascending order or descending order. If True, ranks according to ascending order. If False, ranks according to descending order. False by default.
tie_compensation	Optional. Determines whether to increment ranking for ties -- when two or more numbers in numlist match num. tie_compensation averages the rankings that tied numbers would have received if tied numbers were given different ranks. For example, if two numbers are tied and the ranks that would have applied to them are 3 and 4, tie_compensation averages their rank to 3.5. Without tie compensation, both would be ranked 3. If three numbers are tied and their ranks would have been 5, 6, and 7, their ranks are averaged to 6, instead of giving all three of them a rank of 5. If True, rank is incremented by half of one less than the quantity of numbers in the tie. For example: If numlist consists of the numbers 10, 20, and 30, their rankings are 3, 2, and 1. No tie compensation, because there were no ties. If numlist consists of the numbers 10, 20, 20, and 30, their rankings are 4, 2.5, 2.5, and 1. There are two numbers in a tie (20 and 20). One less than two is one. Half of one is 0.5. Therefore, the rank of the tied numbers are each incremented by 0.5 -- from 2 to 2.5. If numlist consists of the numbers 10, 20, 20, 20, and 30, their rankings are 5, 3, 3, 3, and 1. There are three numbers in a tie (20, 20, and 20). One less than three is two. Half of two is 1. Therefore, the rank of the tied numbers are each incremented by 1 -- from 2 to 3. If numlist consists of the numbers 10, 20, 20, 20, 30, 30, and 40, their rankings are 7, 5, 5, 5, 2.5, 2.5, and 1. There are three numbers in one tie (20, 20, and 20), and two numbers in another tie (30 and 30): One less than three is two. Half of two is 1. Therefore, the rank of the tied numbers 20, 20, and 20 are each incremented by 1 -- from 4 to 5. One less than two is one. Half of one is 0.5. Therefore, the rank of the tied numbers 30 and 30 are each incremented by 0.5 -- from 2 to 2.5. If False, no tie compensation is applied. False by default.