PrecisionCalc
xl Precision
Get Your Numbers Right

 

xlpSORT

Sorts a range of numbers, with up to 32,767 significant digits of precision.

xlpSORT must be array-entered. To array-enter an xlpSORT formula, follow these steps:

  1. Select the cells in which you want the sorted values. The selection can include as many or as few rows and columns as desired.
  2. With the cells selected, type the formula in the first (top or left) of the selected cells.
  3. With the cells still selected, press and hold down the CTRL and SHIFT keys.
  4. With the cells still selected, and with the CTRL and SHIFT keys still held down, press Enter.
  5. You should see the sorted results in the selected cells.

 

Requires the 5,000 SD edition of xlPrecision or higher, or the Free Edition.

 

Syntax

xlpSORT(numlist,descending,left_to_right,fit_to_array,blank_text)

numlist Required. The range of cells with the numbers to sort.
descending Optional. If True, sorts from higher numbers to lower numbers. If False, sorts from lower numbers to higher numbers. False by default.
left_to_right Optional. Determines direction of sorting if the xlpSORT array formula is on multiple rows and columns. If True, sorts the numbers left to right. If False, numbers are sorted top to bottom. False by default.
fit_to_array Optional. Determines the dimensions of the sorted array of numbers if the xlpSORT array formula does not have the same dimensions as numlist. If True, sorts into the xlpSORT array formula's dimensions. If false, numlist's dimensions are retained. False by default.
blank_text Optional. If the xlpSORT array formula contains more cells than the number of cells in numlist, this text is used in the extra cells. Blank by default.

Remarks

Examples

Formula Description Result
=xlpSORT(A1:C5)
 (array-entered in A10:C14)
 
  A B C
1  10  20  70
2  120  30  110
3  90  60  ABC
4  40    150
5  80  140  100
Sort the numbers in A1:C5 in ascending order, from top to bottom, and retaining the dimensions of numlist.  
 
 
  A B C
10  10  70  120
11  20  80  140
12  30  90  150
13  40  100  ABC
14  60  110  
=xlpSORT(A1:C5,TRUE)
 (array-entered in A10:C14)
 
  A B C
1  10  20  70
2  120  30  110
3  90  60  ABC
4  40    150
5  80  140  100
Sort the numbers in A1:C5 in descending order, from top to bottom, and retaining the dimensions of numlist.  
 
 
  A B C
10  150  90  30
11  140  80  20
12  120  70  10
13  110  60  ABC
14  100  40  
=xlpSORT(A1:C5,,TRUE)
 (array-entered in A10:C14)
 
  A B C
1  10  20  70
2  120  30  110
3  90  60  ABC
4  40    150
5  80  140  100
Sort the numbers in A1:C5 in ascending order, from left to right, and retaining the dimensions of numlist.  
 
 
  A B C
10  10  20  30
11  40  60  70
12  80  90  100
13  110  120  140
14  150    ABC
=xlpSORT(A1:C5,,,TRUE)
 (array-entered in A10:B17)
 
  A B C
1  10  20  70
2  120  30  110
3  90  60  ABC
4  40    150
5  80  140  100
Sort the numbers in A1:C5 in ascending order, from top to bottom, into the xlpSORT formula's dimensions.  
 
 
  A B
10  10  100
11  20  110
12  30  120
13  40  140
14  60  150
15  70  ABC
16  80  
17  90  
=xlpSORT(A1:C5,TRUE,TRUE,TRUE,"---")
 (array-entered in A10:D14)
 
  A B C
1  10  20  70
2  120  30  110
3  90  60  ABC
4  40    150
5  80  140  100
Sort the numbers in A1:C5 in descending order, from left to right, into the xlpSORT formula's dimensions, and displaying "---" in the unused cells.  
 
 
  A B C D
10  150  140  120  110
11  100  90  80  70
12  60  40  30  20
13  10  ABC    ---
14  ---  ---  ---  ---
=xlpSORT(A1:C5,,,,"---")
 (array-entered in A10:D15)
 
  A B C
1  10  20  70
2  120  30  110
3  90  60  ABC
4  40    150
5  80  140  100
Sort the numbers in A1:C5 in ascending order, from top to bottom, retaining the dimensions of numlist, and displaying "---" in the unused cells.  
 
 
  A B C D
10  10  70  120  ---
11  20  80  140  ---
12  30  90  150  ---
13  40  100  ABC  ---
14  60  110    ---
15  ---  ---  ---  ---
=xlpSORT(A1:A17)
 (array-entered in A20:D36)
 
  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  1,234,567,890,123,456,789.012345
12  $1,234,567,890,123,456,789.012345
13  1234567890123456789.012345
14  $100000000000000000000
15  $1000
16  5
17  1.00000000000000000000000000001
Sort the numbers in A1:C5 in ascending order, from top to bottom, and retaining the dimensions of numlist.  
 
 
  A
20  1.00000000000000000000000001
21  5
22  $1,000
23  $1000
24  1,234,567,890,123,456,789.012345
25  $1,234,567,890,123,456,789.012345
26  1234567890123456789.012345
27  $100000000000000000000
28  100000000000000000000.0001
29  100000000000000000000.001
30  100000000000000000000.01
31  100000000000000000000.1
32  1000000000000000000000.0001
33  1000000000000000000000.001
34  1000000000000000000000.01
35  1000000000000000000000.1
36  10000000000000000000000.1

See Also

xlpLARGE
xlpRANK
xlpSMALL