[Solved] Sorting random numbers

Discuss the spreadsheet application

[Solved] Sorting random numbers

Postby jrkrideau » Tue Jul 06, 2010 11:33 pm

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.
AOO 4.0.1, Ubuntu 13.04
jrkrideau
 
Posts: 1913
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Sorting random numbers

Postby floris v » Wed Jul 07, 2010 12:28 am

Confirmed, it seems that the values are recalculated at each sort action.
MS Windows Vista - AOO 4.0.1 - LibreOffice 4.1.0.4.
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
floris v
Moderator
 
Posts: 3034
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Sorting random numbers

Postby acknak » Wed Jul 07, 2010 1:27 am

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).
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17424
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Sorting random numbers

Postby MrProgrammer » Wed Jul 07, 2010 5:08 am

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
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.6.8.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 1750
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sorting random numbers

Postby Cambirder » Wed Jul 07, 2010 12:49 pm

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 79 times
OOo 3.3 on Windows 7 & 3.2.1 on Mint 10
User avatar
Cambirder
Volunteer
 
Posts: 647
Joined: Thu Nov 22, 2007 1:01 am

Re: Sorting random numbers

Postby acknak » Wed Jul 07, 2010 2:11 pm

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!
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17424
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Sorting random numbers

Postby MrProgrammer » Wed Jul 07, 2010 7:03 pm

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
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.6.8.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 1750
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sorting random numbers

Postby floris v » Wed Jul 07, 2010 7:22 pm

Why do you actually want to sort a list of random numbers? No sarcasm or whatever intended - I'm really interested.
MS Windows Vista - AOO 4.0.1 - LibreOffice 4.1.0.4.
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
floris v
Moderator
 
Posts: 3034
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands

Re: Sorting random numbers

Postby MrProgrammer » Wed Jul 07, 2010 7:52 pm

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
OpenOffice 3.2.0 Build 9483 on Mac OS X 10.6.8.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Volunteer
 
Posts: 1750
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Sorting random numbers

Postby Cambirder » Thu Jul 08, 2010 12:04 pm

You could achieve it using a simple macro as in the attached example.
Attachments
Sort random numbers2.ods
(10.53 KiB) Downloaded 106 times
OOo 3.3 on Windows 7 & 3.2.1 on Mint 10
User avatar
Cambirder
Volunteer
 
Posts: 647
Joined: Thu Nov 22, 2007 1:01 am

Re: Sorting random numbers

Postby jrkrideau » Fri Jul 09, 2010 1:22 am

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:
AOO 4.0.1, Ubuntu 13.04
jrkrideau
 
Posts: 1913
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Sorting random numbers

Postby jrkrideau » Fri Jul 09, 2010 1:25 am

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.
AOO 4.0.1, Ubuntu 13.04
jrkrideau
 
Posts: 1913
Joined: Sun Dec 30, 2007 10:00 pm
Location: Kingston Ontario Canada

Re: Sorting random numbers

Postby floris v » Fri Jul 09, 2010 10:08 am

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:
MS Windows Vista - AOO 4.0.1 - LibreOffice 4.1.0.4.
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
floris v
Moderator
 
Posts: 3034
Joined: Wed Nov 28, 2007 1:21 pm
Location: Netherlands


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 16 guests