Random Number
 

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
--
Wendy

Random Number
 

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

"Wendy" skrev i melding
...
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
--
Wendy

Random Number
 

Here is a formual solution, with a twist.

First, ensure cell A1 is empty and goto ToolsOptions 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(B10,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)

"Harald Staff" wrote in message
...
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

"Wendy" skrev i melding
...
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
--
Wendy

Random Number
 

Hello Wendy,

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

HTH,
Bernd

Random Number
 

"Wendy" skrev i en meddelelse
...
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
--
Wendy

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



--
Best regards
Leo Heuser

Followup to newsgroup only please.