Random Numbers between 0 and 31 in Excel 3

[qwasy]

hi guys - especially Zathras

(look at thread56-1281655 - 22 Sep 06)

I need to fill a column with random the numbers 0-31.

maestro Zathras wrote nice and short:

A1: =MID(W1,INT(RAND()*(11-ROW(W1)))+1,1)+0
W1: '0123456789
W2: =LEFT(W1,FIND(A1,W1)-1)&MID(W1,FIND(A1,W1)+1,99)

i tried to modify this solution (W1) for numbers 01 02 03 04 ..... 29 30 31 (days of month) - unsuccessfully

would you be or anybody so kind and help me ?

thanking you in advance

 

[AnotherHiggins]

qwasy,

Actually, since you want days of the month you can take advantage of how Excel sees dates. basically, all dates are stored as a whole number (the number of days since 1/1/1900) and all times as a decimal. For more info on this, see faq68-5827.

You should be able to do this without any hidden columns - just one short 'n' sweet formula.

What exactly you need depends on what you plan on doing with these random values.

1) If you want actual numbers returned, but only need them to display between 0-31, then you can use something like
[tab]=Int(Rand()*100)
Then format the cell to custom, DD

2) If you need text that looks like numbers (01, 02, etc.), then you can use
[tab]=Text(Int(Rand()*100),"DD")

3) If you need numbers and the numbers themselves must be between 0-31 (again, see the FAQ for the differenece between this and option 1), then you could use
[tab]=Value(Text(Int(Rand()*100),"DD"))

One final question - if you want days of the month are you sure you really want to include zeros?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[macropod]

Actually, to get random nunbers from 1 to 31 only, you need:
=INT(RAND()*31)+1
If you use =Int(Rand()*100), you'll get random nunbers from 0 to 99.

Cheers

[MS MVP - Word]

 

[strongm]

Given the thread that qwasy specifically references, I think we'll find that they want all the numbers 1 to 31 but in a random order, not just a random number between 0 and 31

 

[KenWright]

Then just put in 0 to 31 in order in say A1:A32, in the column next to it use a random number function (in say B1:B32), then select all the data and sort on the random number column.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[combo]

Here's a trick to quickly get a stable random permutation of given data set:
1) in col. B write the data set you want to randomly mix (say, numbers 1-31: 1 in A1, 2 in A2 etc.),
2) in col. A on the left of data in col. B add =RAND(),
3) sort col. A.
If you need another permutation, just sort col. A again. This works for any data type (numbers, strings).

combo

 

[qwasy]

guys - thanks.

i wasn't be clear enough. i use =ROUND(RAND()*30,0)+1. function return random numbers, some times with repetition, but i need only these 31 numbers once - no matter of order.

Zathras solution is perfect from 0 to 9 and. i am not excel geek, so i am asking for some help.

TIA

 

[combo]

Did you try my post? In addition to it: if one of formulas refers to number in col. B, the reference address will stay unchanged, but will point to new number. To sort only requierd range (RAND functions in col. A and numbers in col. B) this range has to be isolated.

combo

 

[SkipVought]

"Zathras solution is perfect from 0 to 9 "

And it is, for 0 to 9.

But his solution uses a FIND function, so searching for 2-digit numbers will not work consistently.

Check out combo's approch. It is sound.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[strongm]

>Check out combo's approch

I think KenWright suggested it first ...

 

[SkipVought]

Yes, I was Ken, Wright as he is! ;-)

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[SkipVought]

Try this, compensated for false finds...
[tt]
A1: =TEXT(MID(W1,INT(RAND()*(LEN(W1)/2))*2+1,2)+0,"00")
W1: '01020304050607080910111213141516171819202122232425262728293031
W2: =LEFT(W1,IF(MOD(FIND(A1,W1),2)=1,FIND(A1,W1),FIND(A1,W1,FIND(A1,W1)+1))-1)&MID(W1,IF(MOD(FIND(A1,W1),2)=1,FIND(A1,W1),FIND(A1,W1,FIND(A1,W1)+1))+2,99)

[/tt]


Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[AnotherHiggins]

Given the thread that qwasy specifically references, I think we'll find that they want all the numbers 1 to 31 but in a random order, not just a random number between 0 and 31

oooOOOOOH! I see now.

Here's a revised version of my contribution from that other thread. It uses hidden columns and doesn't require manual sorting.
[ul]
[li]In column A, list the numbers you want randomized. In this case, 1-31[/li]
[li]In B1, type in =rand()[/li]
[li]Fill down to B31[/li]
[li]Hide Columns A & B
(or whatever columns you use for this step)[/li]
[li]In C1, type in [COLOR=blue white]=SUMIF($B$1:$B$31, SMALL($B$1:$B$31,A1),$A$1:$A$31)[/color]
(change column references if necessary)[/li]
[li]Fill down to C31[/li]
[/ul]
Column C will now have a randomized list of values from 1-31

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Or, in column C you could use:
[COLOR=blue white]=RANK(B1,$B$1:$B$31)[/color]

(That was GlennUK's post immediately after mine)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[KenWright]

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[qwasy]

hi - my apologize for delay

combo solution (1 Oct 07 8:19) is good - thx.

my next approach will be SkipVought proposal (1 Oct 07 10:03).

thanks again for your help

 

[onedtent]

qwasy, after all that trouble people went through, did u run short of a star? u can borrow mine if u wish

- onedtent Onedtent OnedTent OnedTenT OneDTenT
Trying not to answer posts that have been replied to

 

[qwasy]

thx onedtent - star for combo & SkipVought

 

[SkipVought]

What a COMBO! ;-)

Thanx!

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[combo]

Skip this!

combo