Random Number Generator - Multiple, Non-repeating, results

[Guest]

Is there a way to produce multiple, non-repeating, intergers within a given
range?For example, generate 3 whole numbers between 1 and 20 (with an answer
such as 4,7,18)

I need help! Thanks

 

[Domenic]

Try the following formula, which needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

B2, copied down to B4:

=SMALL(IF(ISNA(MATCH(ROW($B$2:$B$21)-ROW($B$2)+1,$B$1:B1,0)),ROW($B$2:$B$
21)-ROW($B$2)+1),INT(RAND()*(20-ROW()+ROW($B$2)))+1)

Hope this helps!

 

[T. Valko]

Why are you using this:

ROW($B$2:$B$21)-ROW($B$2)+1

Why not just:

ROW($1:$20)

Biff

 

[T. Valko]

Nevermind!

Row insertions!

Biff

Why are you using this:

ROW($B$2:$B$21)-ROW($B$2)+1

Why not just:

ROW($1:$20)

Biff

 

[JE McGimpsey]

One way:

http://mcgimpsey.com/excel/udfs/randint.html

 

[Bernd]

Hello,

Can't you just delete
"nCount = 1 Then
RandInt = CLng((nEnd - nStart) * Rnd() + nStart)
ElseIf"
in your randint UDF?

http://www.sulprobil.com/html/uniqrandint.html

Regards,
Bernd

 

[vezerid]

Domenic,

this is a very nice formula!

Regards,
Kostis

 

[Guest]

Domenic,

I don't know what I did wrong. I entered the formula in B2 and attempted to
copy it through B4, but I only got #NUM! in each cell. Any thoughts on what
I did wrong?

A Long

 

[Domenic]

The formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just
ENTER. Excel will automatically place braces {...} around the formula,
indicating that you've entered it correctly.

Hope this helps!

 

[JE McGimpsey]

Could, but it's much faster to calculate one number than to go through
the rest of the routine if the UDF is called from a single cell.

Won't be even the blink of an eye if there's only one call during a calc
cycle, but can add up if there are lots of multiple calls and multiple
iterations, and it adds only a few bytes to the code.

 

[Guest]

It works! Not sure how, but it works! I'll study it to try to understand it.

Thank you very much

A Long

 

[Guest]

I got it to work for the range 1-20, but am not sure what to change in the
formula to make the range 10-19. Sorry to be a bother and a dummy.

Thanks
A Long

 

[Domenic]

Try...

B2, copied down:

=SMALL(IF(ISNA(MATCH(ROW($B$10:$B$19),$B$1:B1,0)),ROW($B$10:$B$19)),INT(R
AND()*(10-ROW()+ROW($B$2)))+1)

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Guest]

I have entered the formula and checked it multiple times. However, every
time I get a message that the formula has an error. The curser moves to the
AND() every time. Should there be logical conditions within the "( )"?

Thanks

A Long

 

[JE McGimpsey]

Domenic's formula linewrapped poorly. Try:

=SMALL(IF(ISNA(MATCH(ROW($B$10:$B$19),$B$1:B1,0)),
ROW($B$10:$B$19)), INT(RAND()*(10-ROW()+ROW($B$2)))+1)

 

[Domenic]

In this part of the formula...

INT(RAND()*(10-ROW()+ROW($B$2)))+1

....you probably have a line break...

INT(R
AND()*(10-ROW()+ROW($B$2)))+1

Remove it and I think you should be okay.

Hope this helps!

 

[Bernd]

Hello again,

....

Won't be even the blink of an eye if there's only one call during a calc
cycle, but can add up if there are lots of multiple calls and multiple
iterations, and it adds only a few bytes to the code.

....

Hmm, if it comes to speed, IMHO VBA should not be used in this
"pathological" case anyway. A simple worksheet formula would do...

Regards,
Bernd

 

[JE McGimpsey]

Hmm, if it comes to speed, IMHO VBA should not be used in this
"pathological" case anyway. A simple worksheet formula would do...

Sure. You could say that about most VBA UDFs, though - far more
efficient to implement in XL. The UDF overhead is a killer.

But since the branch for a single cell doesn't add more than a
nanosecond or two, and a dozen or so bytes, my thought was to allow for
the pathological. Won't argue it's right or wrong, just the style I
chose.

 

[Bernd]

Agreed. This special case is a substitution of RANDBETWEEN. Regards,
Bernd