random between function

[Arthur]

Hello
I use the random between function to randomly select employees for testing.
I've outlined the problem I'm having below.

Each employee is required to be randomly tested and has a unique number
assigned to them (1-191).

The formula =randbetween(1-191) works well for my purposes when all of the
ones being tested fall between those parameters, but there are many instances
when I want to add specific numbers to the random selection process.....or
not use some of them in that group.

Example 1: select (49,80) and include 136 and/or 141 to that random selection.

Example 2: select (49,80) and not use 59 and/or 72

I'm sure there's a simple way of adjusting the formula as needed, but I
haven't been able to figure it out. Can you help?

Thanks
Arthur

 

[Bernd P]

Hello,

...
Example 1: select (49,80) and include 136 and/or 141 to that random selection.

Select (49,82) and treat 81 as 136 and 82 as 141.

...
Example 2: select (49,80) and not use 59 and/or 72

Select (49,78) and treat 59 as 79 and 72 as 80.

You can do this with a helper cell - don't call RandBetween twice

Regards,
Bernd

 

[Arthur]

Thank you for your reply. I understand what you’re saying but it doesn’t
solve the problem using keystrokes. What if I have the lowest number as 1 and
the highest 191 and there are 25 people eligible for testing……that means I
would have to substitute 166 names…or allow the random between (1-191) to run
and cross out 166 names. If I have 50 eligibles, then I would want to
randomly select 50 names only and not 191.
Thanks again
Arthur

 

[JoeU2004]

What if I have the lowest number as 1 and the highest
191 and there are 25 people eligible for testing

Put the 25 numbers in a range of cells, say A1:A25, then use the following
to select randomly:

=index(A1:A25,randbetween(1,25))


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

 

[Bernd P]

Hello,

Aha. You seem to need my UDF VBUniqRandInt:
http://www.sulprobil.com/html/uniqrandint.html

Please note that its the second macro, not the first on that page.

Regards,
Bernd

 

[Arthur]

Thanks Joe.
I tried it on a few samples and "like magic", the random numbers appeared
with just those employees I wanted to be in the mix. Really appreciate the
help.
Thanks again
Arthur

 

[Arthur]

Thank you for your help and quick response Bernd. I looked at the unique rand
integer you suggested and while I'm certain it works....it's looks to be
beyond my capabilites to install. The last real programming I did was when I
took some computer courses some time ago....and before that I was thrilled
when I was able to watch my name appear on the computer screen 1000's of
times on the vic20.

If I had a tech guy at work, I would have handed it to him and said "here,
install this". But I don't. Fortunately Joe provided a simple program that
seems to work and should be adequate.

Thanks again
Arthur

 

[Bernd P]

Hello,

No problem. We all just try to help a little bit.

If I do not err, Joe's solution can produce duplicates.

If that's no harm to you...

Regards,
Bernd

 

[JoeU2004]

If I do not err, Joe's solution can produce duplicates.

.... And it is volatile; that is, it changes every time any cell in the
workbook is edited :-(.

But since the OP said he uses RANDBETWEEN already, I presumed that these
were not problems for him -- much to my surprise, I might add.


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