[Solved] Sorting random numbers

Discuss the spreadsheet application
Locked
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

[Solved] Sorting random numbers

Post by jrkrideau »

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
Last edited by jrkrideau on Fri Jul 09, 2010 11:40 am, edited 1 time in total.
LibreOffice 7.3.7. 2; Ubuntu 22.04
User avatar
floris v
Volunteer
Posts: 4422
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Sorting random numbers

Post by floris v »

Confirmed, it seems that the values are recalculated at each sort action.
OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Sorting random numbers

Post by acknak »

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).
AOO4/LO5 • Linux • Fedora 23
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sorting random numbers

Post by MrProgrammer »

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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Cambirder
Volunteer
Posts: 647
Joined: Thu Nov 22, 2007 1:01 am

Re: Sorting random numbers

Post by Cambirder »

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.
Attachments
Sort random numbers.ods
(8.31 KiB) Downloaded 190 times
OOo 3.3 on Windows 7 & 3.2.1 on Mint 10
User avatar
acknak
Moderator
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Sorting random numbers

Post by acknak »

No, a sort also resets them.
Oops :o

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

Good catch!
AOO4/LO5 • Linux • Fedora 23
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sorting random numbers

Post by MrProgrammer »

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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
floris v
Volunteer
Posts: 4422
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Sorting random numbers

Post by floris v »

Why do you actually want to sort a list of random numbers? No sarcasm or whatever intended - I'm really interested.
OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
User avatar
MrProgrammer
Moderator
Posts: 4894
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sorting random numbers

Post by MrProgrammer »

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.
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
Cambirder
Volunteer
Posts: 647
Joined: Thu Nov 22, 2007 1:01 am

Re: Sorting random numbers

Post by Cambirder »

You could achieve it using a simple macro as in the attached example.
Attachments
Sort random numbers2.ods
(10.53 KiB) Downloaded 250 times
OOo 3.3 on Windows 7 & 3.2.1 on Mint 10
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Sorting random numbers

Post by jrkrideau »

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. :roll:
LibreOffice 7.3.7. 2; Ubuntu 22.04
jrkrideau
Volunteer
Posts: 3816
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Sorting random numbers

Post by jrkrideau »

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.
LibreOffice 7.3.7. 2; Ubuntu 22.04
User avatar
floris v
Volunteer
Posts: 4422
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Sorting random numbers

Post by floris v »

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. :lol:
OpenOffice 4.1.11 on Ubuntu; LibreOffice 6.4 on Linux Mint, LibreOffice 7.6.2.1 on Ubuntu
If your problem has been solved or your question has been answered, please edit the first post in this thread and add [Solved] to the title bar.
Nederlandstalig forum
Locked