random between function

A

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
 
B

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
 
A

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
 
J

JoeU2004

Arthur said:
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 -----
 
A

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
 
A

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
 
B

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
 
J

JoeU2004

Bernd P said:
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 -----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top