Sort function autocorrects randbetween() functions. How to stop this autocorrect?

Question

In column A, I have a list of numbers in sequence, 1,2,3,4...

In column B, I have randbetween() functions for each 2 numbers in sequence. Example:

Column A    Column B

1                        =randbetween(A1, A2)

2                        =randbetween(A1,A2)

3                        =randbetween(A3,A4)

4                        =randbetween(A3,A4)

5                        =randbetween(A5,A6)

6                        =randbetween(A5,A6)

So in this way, in column B, I have randomized numbers within each 2 numbers. Column B could come out to be like [1,1, 4, 3, 5, 6...]

However, when I sort Column B, SOME of the randbetween functions would change automatically to look more like this:

1                        =randbetween(A1, A2)

2                        =randbetween(A2,A3)

3                        =randbetween(A3,A4)

4                        =randbetween(A4,A5)

5                        =randbetween(A5,A6)

6                        =randbetween(A6,A7)

Which I DON'T want.

Ultimately, what I want to do is to randomize pairs of words. I have a list of word pairs like (mail/nail, meat/neat, etc) and I want to randomize the pairs within the word list. So this is how I tried to randomize them, by attaching the word to the randomized number, then sorting them. Any help would be greatly appreciated. Thanks!

Answer 1

You solved my problem with this:

"And you might be able to achieve that by changing the relative references to absolute references, e.g. RANDBETWEEN($A$1,$A$2)."

Thanks!!

And for clarity purposes, Column A would stay stationary. I would sort only Column B. In this way, Column B will randomize and have numbers like [1,1, 3,4, 6,6, 8,7..]. In Column C, I would list words in pairs like [ mail, nail, meat, neat]. Then when I sort ONLY Column B and C, based on Column B, (in which the rand would be recalculated after the sort), I would get a randomized pairs of words.

Anyway, thanks again!!!

Answer 2

Vicky wrote:

In column A, I have a list of numbers in sequence, 1,2,3,4...

In column B, I have randbetween() functions for each 2 numbers in sequence. Example:

Column A    Column B

1                        =randbetween(A1, A2)

2                        =randbetween(A1,A2)

[....]

However, when I sort Column B, SOME of the randbetween functions would change automatically to look more like this:

1                        =randbetween(A1, A2)

2                        =randbetween(A2,A3)

[....]

Which I DON'T want.

It is unclear to me what you expect.  I suppose you would like column B to remain the same; that is, RANDBETWEEN in the same order.  And you might be able to achieve that by changing the relative references to absolute references, e.g. RANDBETWEEN($A$1,$A$2).

But after the sort, A1 might be greater than A2.  In that case, RANDBETWEEN returns a #NUM error.

Vicky wrote:

Ultimately, what I want to do is to randomize pairs of words. I have a list of word pairs like (mail/nail, meat/neat, etc) and I want to randomize the pairs within the word list. So this is how I tried to randomize them, by attaching the word to the randomized number, then sorting them.

So why do you care what is in column B after the sort?  So you can sort again later?

Perhaps a better way to do this is:  but the pair of words in adjacent cells in a row, e.g. columns A and B, and put =RAND() into column C.  Now you can sort A:C based on column C.

When you are done, column C will have new values that make it appear that columns A and B were not sorted in order.  This is just the way that Excel works with volatile formulas:  they are recalculated after the sort.