Repeat random numbers from a range and text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

kitkat1 - 08 Jul 2006 20:25 GMT

I am new at this, I need to create 2 different types of randomness, one of
numbers and one of text selections.

#1 a range of numbers, i.e, 134-2807 that allows repeat of numbers with a
return of only 25 numbers. This random option is use daily so I need to be
able to create a worksheet where each day only the range is changed, (without
having to key the each number of the range to a separate row line)

#2 a range of 5 process types, i.e, data, irsdat, ardat, ispdat, dsldat,
where 3 processes are selected daily from the 5 listed which allows repeat of
processes.

I reviewed the RAND and RANDBETWEEN help files but I am confused if the
formulas are keyed to the same worksheet where the random options are
generated. I would actually like to have in both cases above, the random
selections generated to a separate worksheet. Where do I start?

Reply to this Message

RagDyeR - 08 Jul 2006 21:10 GMT

For #1
Enter this formula and copy down 25 rows:

=INT(RAND()*2674+134)

Hit <F9> to get a new list of random numbers.

Note: You did say to allow repeats!

For #2
Enter this formula and copy down 3 rows:

=INDEX({"data","irsdat","ardat","ispdat","dsldat"},INT(RAND()*5)+1)

AGAIN:
Hit <F9> to get a new list of random numbers.

Note: You did say to allow repeats!

Signature

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I am new at this, I need to create 2 different types of randomness, one of
numbers and one of text selections.

#1 a range of numbers, i.e, 134-2807 that allows repeat of numbers with a
return of only 25 numbers. This random option is use daily so I need to be
able to create a worksheet where each day only the range is changed,
(without
having to key the each number of the range to a separate row line)

#2 a range of 5 process types, i.e, data, irsdat, ardat, ispdat, dsldat,
where 3 processes are selected daily from the 5 listed which allows repeat
of
processes.

I reviewed the RAND and RANDBETWEEN help files but I am confused if the
formulas are keyed to the same worksheet where the random options are
generated. I would actually like to have in both cases above, the random
selections generated to a separate worksheet. Where do I start?

Reply to this Message

kitkat1 - 08 Jul 2006 23:15 GMT

Thank you for your help, two more questions if I may.

1). Is there a way to stop the random list from changing, can I link it to
another worksheet that is potected to prevent losing the original selection?

2). Also, if my number range changes all I have to do is change the number
range in the formula would this be correct?

>For #1
>Enter this formula and copy down 25 rows:
[quoted text clipped - 33 lines]
>generated. I would actually like to have in both cases above, the random
>selections generated to a separate worksheet. Where do I start?

Reply to this Message

Ragdyer - 09 Jul 2006 03:31 GMT

First of all, you should turn *off* automatic calculation.
<Tools> Options> <Calculation> tab,
And click on "Manual" under 'Calculation', then <OK>.

[ #1 ]
Now, select the 25 numbers and right click in the selection, and choose
"Copy".
Navigate to the new location (either other sheet or other workbook), and
right click in the top cell of this new location and choose "Paste Special".
Then click on "Values", then <OK>.

What you have done here is *just copied* the numbers (values), *not* the
formulas behind the numbers.
This way the numbers *cannot* change when you calculate any new sets.

With the calculation set to "Manual", hitting <F9> *still* (manually)
calculates the workbook, and gives you a new random set.

[ #2 ]
The formula for returning random numbers between 2 chosen numbers is:

RAND()*(b-a)+a
With "a" as the minimum limit,
And "b" as the maximum limit.

*HOWEVER*, this returns decimals.
So, when we wrap the formula in the INT() function, to return an integer,
the formula changes slightly to:

=INT(RAND()*(b+1-a)+a)

So all you have to do to change your range of numbers is plug them into the
above (INT) formula.

Signature

HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

> Thank you for your help, two more questions if I may.
>
[quoted text clipped - 41 lines]
> >generated. I would actually like to have in both cases above, the random
> >selections generated to a separate worksheet. Where do I start?

Reply to this Message

kitkat1 - 10 Jul 2006 11:44 GMT

Thank you so much Ragdyer. I do understand this logic now that I have used
the examples provided to me. Thank you for your help and patience. Have a
great week!

>First of all, you should turn *off* automatic calculation.
><Tools> Options> <Calculation> tab,
[quoted text clipped - 35 lines]
>> >generated. I would actually like to have in both cases above, the random
>> >selections generated to a separate worksheet. Where do I start?

Reply to this Message

RagDyeR - 10 Jul 2006 15:44 GMT

Thank you for the feed-back.

Signature

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

Thank you so much Ragdyer. I do understand this logic now that I have used
the examples provided to me. Thank you for your help and patience. Have a
great week!

Ragdyer wrote:

Reply to this Message

Rate this thread: