Random Number Generator - Multiple, Non-repeating, results

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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!
 
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
 
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!
 
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.
 
It works! Not sure how, but it works! I'll study it to try to understand it.

Thank you very much

A Long
 
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
 
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!
 
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
 
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)
 
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!
 
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
 
Bernd said:
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.
 
Back
Top