How to automatically sort random numbers

Question

I've got 2 columns in a spreadsheet.

Col. A contains a formula that generates a random number:

=RANDBETWEEN(1,100)

Col. B calculates the rank of the random number in A using

=RANK.AVG(A2,A$2:A$274).  

I would like to sort Col. B but I can't because the random numbers recalculate ever time I hit the SORT button.

Is there any way to make a column of random numbers that will self sort?

Answer 1

Try some variation of this:

A    B         C
1    =rand()    =large($b$1:$b$10,a1)
2    =rand()    =large($b$1:$b$10,a2)
3    =rand()    =large($b$1:$b$10,a3)
4    =rand()    =large($b$1:$b$10,a4)
5    =rand()    =large($b$1:$b$10,a5)
6    =rand()    =large($b$1:$b$10,a6)
7    =rand()    =large($b$1:$b$10,a7)
8    =rand()    =large($b$1:$b$10,a8)
9    =rand()    =large($b$1:$b$10,a9)
10   =rand()    =large($b$1:$b$10,a10)

I don't totally understand the order of calcs and recalcs in excel, but this seems to work once you have recalculated at least once (Column C may not be ordered when you first paste in, but after a recalc it will).

Máté Juhász points out that you could also replace =large($b$1:$b$10,a1) with =large($b$1:$b$10,row() ), which would cut the need for column A entirely. In this examples, I have two header rows, so I adjust row() by 2.

A         B
Random Number Tables
Unsorted    Sorted 
=rand()    =large($a$2:$a$12,row()-2)
=rand()    =large($a$2:$a$12,row()-2)
=rand()    =large($a$2:$a$12,row()-2)
=rand()    =large($a$2:$a$12,row()-2)
=rand()    =large($a$2:$a$12,row()-2)
=rand()    =large($a$2:$a$12,row()-2)
=rand()    =large($a$2:$a$12,row()-2)
=rand()    =large($a$2:$a$12,row()-2)
=rand()    =large($a$2:$a$12,row()-2)
=rand()    =large($a$3:$a$12,row()-2)