PrecisionCalc
xl
Precision

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:

• 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, 2, and 1.
• There are two numbers in a tie (20 and 20), so one ranking (rank #3) is skipped.

• If numlist consists of the numbers 10, 20, 20, 20, and 30, their rankings are 5, 2, 2, 2, and 1.
• There are three numbers in a tie (20, 20, and 20), so two rankings (ranks #3 and  #4) are skipped.

• If numlist consists of the numbers 10, 20, 20, 20, 30, 30, and 40, their rankings are 7, 4, 4, 4, 2, 2, and 1.
• There are three numbers in one tie (20, 20, and 20), and two numbers in another tie (30 and 30):
• There are three numbers in one tie (20, 20, and 20), so two rankings (ranks #5 and  #6) are skipped.
• There are two numbers in another tie (20 and 20), so one ranking (rank #3) is skipped.

Syntax

xlpRANK(num,numlist,ascending,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.

Remarks

• numlist can accept cells with both numbers and text.

• numlist can accept cells with text formatted with the local currency symbol and thousands separators, and negatives can be formatted with either a leading hyphen or parentheses.

Examples

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. 3
=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. 11
=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. 2
=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. 7
=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. 8
=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. 11
=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. 12