Apache OpenOffice Community Forum

I have a column of random numbers generated using =rand()

I don't seem to be able to sort them. Is this normal?

Sort works fine if I paste the numbers generated as pure numbers.

I thought that I had read something a few weeks ago about a problem like this but did not find anything exactly the same when I searched the forum.

OOPs, forgot, I'm using 3.2.1. (just installed a few hours ago) despite what my sig says.

Off to update profile

Confirmed, it seems that the values are recalculated at each sort action.

RAND() is recalculated "often" (I don't know exactly what things can trigger it).

If you need something more stable, maybe you can use RANDBETWEEN( low; high ): it changes only with a full recalculation (Ctrl+Shift+F9).

OpenOffice Help wrote:RAND()
This function produces a new random number each time Calc recalculates. ... To generate random numbers which never recalculate, copy cells each containing =RAND(), and use Edit - Paste Special (with Paste All and Formulas not marked and Numbers marked).

I'd say this is working as documented.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

If you need something more stable, maybe you can use RANDBETWEEN( low; high ): it changes only with a full recalculation (Ctrl+Shift+F9)

No, a sort also resets them. You could do it with a second column as per the attached example, where I've generated the random numbers in A and in a second column I've entered =$A$2, =$A$3 ect. sorting this column puts your numbers in acceding or descending order without regenerating them and maintaining the links to the correct cells after sorting.

No, a sort also resets them.

Oops

I was sure it wouldn't, surprised that it does, and embarrassed that I didn't check.

Good catch!

Cambirder wrote:

You could do it with a second column

Be aware that this technique only works until a recalculation occurs. For example, if you save the file and reopen it, the RAND() functions are recalculated and the values that were formerly sorted in column C are (probably) out of order again. I even tried Tools, Protect Document, Sheet. Further sorts on the cells are not allowed but a full recalculate or Save/Open still destroys the sort order. I've wanted to do similar random sorts myself, and was really hoping that this clever technique was the solution, but it appears that it has its limitations.

Why do you actually want to sort a list of random numbers? No sarcasm or whatever intended - I'm really interested.

In my case it was to get a range of data into a different order, perhaps for some testing, though I don't remember. The random numbers were just the sort keys, and I really didn't care about their values. In fact, I probably deleted the column after the sort, so for me it didn't really matter that the sort operation recalculated the keys and therefore they were not ascending after the sort because the sort had achived its purpose of reordering the rows.

You could achieve it using a simple macro as in the attached example.

floris v wrote:Why do you actually want to sort a list of random numbers? No sarcasm or whatever intended - I'm really interested.

A question on one of the fora, from vegasdave who actually wanted to randomly sort words. It seem to me that the easiest way to do this would simply be to turn off autocalc (or whatever it's called) and then just recalculate a random number column and sort on that column.

The idea worked fine until I actually tried a test data set.

Thanks for all the solutions. I'm not sure which one I may use but it was definitely reassuring to find that it was just not me imagining the problem with sort.

If your problem has been solved, please edit the first post in this thread and add [Solved] to the title bar. Please display your updated signature.