[Solved] Sorting random numbers
[Solved] Sorting random numbers
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
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
Re: Sorting random numbers
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
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
Re: Sorting random numbers
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).
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
- MrProgrammer
- Moderator
- Posts: 4894
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Sorting random numbers
I'd say this is working as documented.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).
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).
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).
Re: Sorting random numbers
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.If you need something more stable, maybe you can use RANDBETWEEN( low; high ): it changes only with a full recalculation (Ctrl+Shift+F9)
- Attachments
-
- Sort random numbers.ods
- (8.31 KiB) Downloaded 190 times
OOo 3.3 on Windows 7 & 3.2.1 on Mint 10
Re: Sorting random numbers
OopsNo, a sort also resets them.
I was sure it wouldn't, surprised that it does, and embarrassed that I didn't check.
Good catch!
AOO4/LO5 • Linux • Fedora 23
- MrProgrammer
- Moderator
- Posts: 4894
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Sorting random numbers
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.Cambirder wrote:You could do it with a second column
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).
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).
Re: Sorting random numbers
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
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
- MrProgrammer
- Moderator
- Posts: 4894
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Sorting random numbers
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).
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).
Re: Sorting random numbers
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
Re: Sorting random numbers
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.floris v wrote:Why do you actually want to sort a list of random numbers? No sarcasm or whatever intended - I'm really interested.
The idea worked fine until I actually tried a test data set.
LibreOffice 7.3.7. 2; Ubuntu 22.04
Re: Sorting random numbers
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
Re: Sorting random numbers
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.
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
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