Excel =RAND()

A

Andrea

When I use the =RAND() command and get a column of random numbers, then sort
both columns, it doesn't seem to me that the numbers are sorted. Maybe I've
been doing this too long and my head is muddled, but are these sorted? And
what am I doing wrong? Here are a few examples.

0.940651
0.6207
0.745175
0.135738
0.269581

and so on.

Also, how can I get the same numbers to stay there without resorting? I
think I'm supposed to press F9, but that just creates a new number! Thank
you.
 
A

Andrea

Andrea said:
When I use the =RAND() command and get a column of random numbers, then
sort both columns, it doesn't seem to me that the numbers are sorted.
Maybe I've been doing this too long and my head is muddled, but are these
sorted? And what am I doing wrong? Here are a few examples.

0.940651
0.6207
0.745175
0.135738
0.269581

and so on.

Also, how can I get the same numbers to stay there without resorting? I
think I'm supposed to press F9, but that just creates a new number! Thank
you.
Click to expand...

Sorry--I forgot to say that I'm using Office 2003 on Windows XP Pro.
 
H

Harlan Grove

Andrea wrote...
When I use the =RAND() command and get a column of random numbers, then sort
both columns, it doesn't seem to me that the numbers are sorted. Maybe I've
been doing this too long and my head is muddled, but are these sorted? And
what am I doing wrong? Here are a few examples.

0.940651
0.6207
0.745175
0.135738
0.269581

and so on.
Click to expand...
....

RAND is a volatile function, which means Excel recalculates all
formulas containing RAND calls whenever ANYTHING triggers
recalculation. Sorting triggers recalculation, so sorting ranges
containing =RAND() formulas results in different, usually unsorted
ranges. ['Usually' because such sorted ranges could be fortuitously
ordered.]

You need to convert these formulas to values. Select the range
containing the =RAND() formulas and run the menu commands Edit > Copy,
then Edit > Paste Special selecting Values in the Paste Special dialog.

Finally, you'd be better off asking questions like this in the Excel
newsgroups rather than general Office newsgroups.
 
A

Andrea

I knew there was something I needed to do to keep the numbers from changing,
but I couldn't remember what and couldn't find it with a Google search. It
was the copy and paste special commands. Thanks. (Next time I'll ask in an
Excel group.)


Harlan said:
Andrea wrote...
When I use the =RAND() command and get a column of random numbers, then
sort both columns, it doesn't seem to me that the numbers are sorted.
Maybe I've been doing this too long and my head is muddled, but are
these sorted? And what am I doing wrong? Here are a few examples.

0.940651
0.6207
0.745175
0.135738
0.269581

and so on.
Click to expand...
...

RAND is a volatile function, which means Excel recalculates all
formulas containing RAND calls whenever ANYTHING triggers
recalculation. Sorting triggers recalculation, so sorting ranges
containing =RAND() formulas results in different, usually unsorted
ranges. ['Usually' because such sorted ranges could be fortuitously
ordered.]

You need to convert these formulas to values. Select the range
containing the =RAND() formulas and run the menu commands Edit > Copy,
then Edit > Paste Special selecting Values in the Paste Special dialog.

Finally, you'd be better off asking questions like this in the Excel
newsgroups rather than general Office newsgroups.
Click to expand...
 
Top