Random Number

[Guest]

There is a function that allows you to select Random Numbers
=randbetween(1,25). This function is available when you add in the Analysis
Toolpak. The problem is that if I copy it over cells it does sometimes
repeat a number. I only want unique numbers can anyone help with this one,
maybe there is another way of getting only unique numbers to appear

 

[Harald Staff]

Hi Wendy

I don't think I've seen a pure formula solution to this one. For unique
numbers, you need a list of all those numbers and then pick a random
selection from it. Like
A1:A25 contains numbers 1 to 25
B1:B25 contains formula =RAND()
Sort by B column.

HTH. Best wishes Harald

 

[Bob Phillips]

Here is a formual solution, with a twist.

First, ensure cell A1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell B1
=IF(($A$1="")+(AND(B1>0,COUNTIF($B$1:$B$25,B1)=1)),B1,RANDBETWEEN(1,25))
it should show a 0

Copy B1 down to B25.

Finally, put some value in A1, say an 'x', and all the random numbers will
be generated, and they won't change.

To force a re-calculation, clear cell A1, edit cell B1, don't change it,
just edit to reset to 0, copy B1 down to B25, and re-input A1.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[bplumhoff]

Hello Wendy,

I suggest to take my function UniqRandInt:
http://www.sulprobil.com/html/uniqrandint.html

HTH,
Bernd

 

[Leo Heuser]

There is a function that allows you to select Random Numbers
=randbetween(1,25). This function is available when you add in the
Analysis
Toolpak. The problem is that if I copy it over cells it does sometimes
repeat a number. I only want unique numbers can anyone help with this
one,
maybe there is another way of getting only unique numbers to appear

Hi Wendy

Here's an UDF(UserDefinedFunction).
If you select 25 cells the numbers 1 through 25 are inserted
at random in the cells. If you select 15 cells, numbers 1 -15
are inserted etc.
From the workbook this is accomplished thus:

1. Select the range
2. While the cells are selected enter the formula
=myrand()
3. Finish with <Shift><Ctrl><Enter> instead of just <Enter>

If you want to start with the number 10 instead of 1 enter

=myrand(10)

to start with -10 instead of 1 enter

=myrand(-10)

etc.

To get a new set of values press <Ctrl><Alt><F9>.


Insert the below function in a general module.


Function MyRand(Optional RandOffset As Long) As Variant
'Leo Heuser, 2 August, 2006
Dim Counter As Long
Dim Counter1 As Long
Dim NumOfElements As Long
Dim Placement As Long
Dim Result() As Variant
Dim RandColumns As Long
Dim RandData() As Variant
Dim RandRows As Long

Randomize

With Range(Application.Caller.Address)
RandRows = .Rows.Count
RandColumns = .Columns.Count
End With

NumOfElements = RandRows * RandColumns

If RandOffset = 0 Then RandOffset = 1

ReDim RandData(1 To NumOfElements)

For Counter = 1 To NumOfElements
RandData(Counter) = Counter + (RandOffset - 1)
Next Counter

ReDim Result(1 To RandRows, 1 To RandColumns)

For Counter = 1 To RandRows
For Counter1 = 1 To RandColumns
Placement = Int(Rnd() * NumOfElements + 1)
Result(Counter, Counter1) = RandData(Placement)
RandData(Placement) = RandData(NumOfElements)
NumOfElements = NumOfElements - 1
Next Counter1
Next Counter

MyRand = Result

End Function