Random Numbers between 0 and 9 in Excel 3

[Beau71]

I need to fill a column with the numbers 0-9 at random. Does anyone know how i would go about doing that? I can't use the RANDBETWEEN function because of the possiblity of the same number in 2 different cells.

 

[DirkStruan]

Try

=round(10*Rand(),0)



Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[DirkStruan]

or

=Int(10*Rand())

(I'm assuming you are after integers here)

D

 

[Beau71]

I think i should clarify exactly what i am trying to do.
Cell, A1:A10 are going to contain a number 0-9. Each number can only be used once. For example if A1 = 4, then no other cell can equal 4.

 

[DirkStruan]

Certainly more tricky - can see why you are asking. Can you use VBA or are we stuck with Excel's functions?

 

[Beau71]

I am up for anything, as long as it works!

 

[DirkStruan]

Ok, this can't be the most efficient but try the following macro. Note it starts putting figures in the activecell and works down the column.

Sub zero_to_nine()
Dim Nos(10)
Dim Chosen(10)

For x = 0 To 9
Nos(x) = x
Chosen(x) = "N"
Next x
counter = 1
While counter < 11
10 random = Int(10 * Rnd())
If Chosen(random) = "N" Then
ActiveCell.Offset(counter - 1, 0).Value = Nos(random)
Chosen(random) = "Y"
counter = counter + 1
End If
Wend
End Sub

D

 

[Zathras]

Here is a non-macro solution. It requires a hidden column. I used column W:

Set up a test worksheet this way:
[tt]
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)
[/tt]
Copy A1 into A2:A10
Copy W2 into W3:W10
Hide column W

You can put this formula somewhere to validate the output:
[tt]
A12: =SUM(A1:A10)
[/tt]

Every time you calculate, the sequence in A1:A10 will change. You may want to turn calculation to manual and hit F9 after all of the other values you need have been entered.

 

[Beau71]

AMAZING, absolutely amazing!!

 

[shellig]

Wow...great answers!
When I want to do this (like randomizing experimental trials), I just put 0-9 in column 1 in order, use the rand() in column 2 and then sort by column 2...
When you sort column 2 changes so if you don't like the mix, you can sort again...otherwise, I typically delete the rand() column...
No coding required. No special formulas.

 

[MeGustaXL]

Almost amazin! But why do I get #VALUE! in A6:A10 and W7:W10?

Chris

Why are there 5 syllables in the word "monosyllabic"? - Stephen Wright

http://www.insys-ltd.co.uk

 

[DirkStruan]

Chris

Given that you've got 5 out of 10 sorted I think you've got a slight error in the formula in the W column and it is removing two numbers at a time rather than one. It's only a guess though.

Did you type in the function or copy & paste it? If you copied & pasted it I'm obviously wrong.

D

 

[MeGustaXL]

Hi Dirk,

I copy/pasted it, and it gives varying numbers of #VALUE! as I re-calculate the sheet

Chris

Why are there 5 syllables in the word "monosyllabic"? - Stephen Wright

http://www.insys-ltd.co.uk

 

[DirkStruan]

Chris

Did you enter 0123456789 in W1 with the ' in front to make it a proper ordered string of length 10?

If not everything can go pear-shaped.

D

 

[MeGustaXL]

Yes. Still pears, though [frown]

Chris

Why are there 5 syllables in the word "monosyllabic"? - Stephen Wright

http://www.insys-ltd.co.uk

 

[DirkStruan]

Sorry Chris, I'm stumped on this one.

If I copy and paste the formulae Zathras supplied I get no problems but slight variations to the formulae results in pears.

Am grasping now but did you try it in a new blank spreadsheet (in case there is anything else, somewhere, interferring with the result)?

D

 

[Rofeu]

Hi,

you can also try the following formula instead of the one in W2:

=SUBSTITUTE(W1,A1,"")

Cheers,

Roel

 

[AnotherHiggins]

Beau71,

Here's another idea using hidden columns. This eliminates any need to sort after re-randomizing.

-In column A, list the numbers you want randomized. In this case, 0-9.[red]*[/red]
-In B1, type in =rand(). Fill down.[red]**[/red]
-Hide Columns A & B (or whatever columns you use for this step)
-In C1, type in [COLOR=blue white]=SUMIF($B$1:$B$10, SMALL($B$1:$B$10,A1+1),$A$1:$A$10)[/color] (change column references if necessary)

Column C will now have a randomized list of values from 0-9!

Some tips, if you don't already know:
[red]*[/red] A quick way to fill a range with incrementing numbers: Type 0 in A1, hover the cursor over the bottom-right corner of the cell until the cursor changes to a bold +. Now press and hold [Ctrl] and right click. Drag down as far as you need.

[red]**[/red] To quickly fill a range as far down as a neighboring column, hover the cursor over the bottom-right corner of the cell until the cursor changes to a bold +. Now Double Click (left mouse button).

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

Help us help you. Please read FAQ181-2886 before posting.

 

[GlennUK]

Put =RAND() in cells B1:B10, and in cell A1 put this formula:

=RANK(B1,$B$1:$B$10)-1

and copy down to A10, and hey presto random numbers from 0 to 9.


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[MeGustaXL]

Zathras and Dirk: Fixed! Just made sure I pasted into the formula bar and not the cell. There appears to be something about the text from the code boxes that turns to pears if you just Ctrl-V it into the cell

Glenn: Nice! That's the way I've done it in the past (Yes! Really!)

Chris

Beer is God's way of telling us He loves us and wants us to be happy - Benjamin Franklin