How to automatically sort random numbers

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?

1 Answer

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)