RANDBETWEEN()

[Paul]

I'm using the formula =RANDBETWEEN(1,15)
to generate random numbers in cells A2 through A6.
I don't want any of those random numbers to be repeated.
Any suggestions on how to do this?
Thanks for your help.

 

[Dave Peterson]

You may want to visit J.E. McGimpsey's site and grab a copy of his =RandInt()
function.
http://www.mcgimpsey.com/excel/udfs/randint.html

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[B. R.Ramachandran]

Hi,

In a helper column, say B1 to B15, enter the formula =RAND(). This will
create 15 random numbers.

Now, in A2, enter the formula, =RANK(B1,$B$1:$B$15,1)
and drag (autofill) the formula down to A6.

Every time you hit F9 button, you will have a new set of non-repeating
random numbers between 1 and15 in A2-A6.

Regards,
B. R. Ramachandran

 

[JoeU2004]

Every time you hit F9 button, you will have a new set of non-repeating
random numbers between 1 and15 in A2-A6.

.... And every time you edit any cell in the workbook :-(.

Presumably that's okay with Paul, since the same is true about RANDBETWEEN.

But one way to avoid that "volatile" behavior is to replace =RAND() with
=myRand(), which is a UDF defined as follows:

Function myRand(Optional arg as Range) as Double
Static first as Integer
If first = 0 Then Randomize: first = 1
myRand = Rnd()
End Function

If you pass a cell or cell range to myRand, it will recalculate whenever any
cell in the range is edited. Otherwise, myRand is recalculated only when
the worksheet is recalculated, e.g. ctrl+alt+F9.


----- original message -----

 

[T. Valko]

using the formula =RANDBETWEEN(1,15) to

generate random numbers in cells A2 through A6.

One way...

This formula references cell A1. A1 must not contain a number from 1 to 15.

Create this named formula:
Goto the menu Insert>Name>Define
Name: Nums
Refers to: ={1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}
OK

Enter this array formula** in A2 and copy down to A6:

=SMALL(IF(ISNA(MATCH(Nums,$A$1:A1,0)),Nums),INT(RAND()*(15-ROW()+ROW(A$2)))+1)

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Jacob Skaria]

Paul, watch this 8 min video on random sampling.

If this post helps click Yes

 

[T. Valko]

That dude sounds like he's half asleep!

 

[Jacob Skaria]

Yes...as if not interested...

That dude sounds like he's half asleep!

 

[B. R.Ramachandran]

I totally agree with you! Also, thanks for the =myRand() UDF.
I wasn't sure whether Paul wants a static set or a volatile regenerating set
of non-repeating random numbers in A2-A6. Since =RANDBETWEEN() is also
volatile, I thought the approach is adequate.

If he wants a static set, he could still follow the approach and do a "copy"
on A2-A6 followed by "paste special'--> "values" on the same location.

Kind regards,
Ramachandran

 

[JoeU2004]

If he wants a static set, he could still follow the approach and do a
"copy"
on A2-A6 followed by "paste special'--> "values" on the same location.

Yes, I do the same for a quick-and-dirty implementation. The problem is:
it is difficult to generate a new set of random numbers quickly.

I prefer to put the =RAND() in some out-of-the-way column, and
copy-and-paste-special-value into the range depended on, B1:B15 in your
case.

Of course, the RAND column keeps generating new random values unnecessarily.
A small performance hit in this case. If it proves costly in larger
problems, obviously is it better to avoid them, either by pasting values
over as you suggest or by using a non-volatile macro.


----- original message -----