How do I change numeric values into predefined text?

[Mister Moe]

I am trying to create a spreadsheet that displays 50
random words taken from a predefined list of 400. I
started by using the following formula:

=ROUND((RAND()*400 + 1),0)

I used this to generate 50 random numbers between 1 and
400. How do I create a formula, or modify my existing
formula so that the 50 random numbers I am generating are
replaced with a corresponding word from my list of 400
Any assistance would be greatly appreciated.

Moe

 

[Bob Phillips]

Moe,

Assuming that the words are in G1:G400, and the random numbers in A1 etc,
try this

=INDEX($G$1:$G$400,A1,1)

One problem, every time the worksheet recalculates, the random numbers will
change as will the aligned words.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[J.E. McGimpsey]

If your words are in A1:A400,

=INDEX(A:A,INT(RAND()*400+1))

and copy down.

Note that using ROUND() in your formula could give you the result
401, so INT() seems to be more appropriate.

Note also that this will not prevent duplicates.

 

[SM Mister Moe]

Thank you so much for your assistance Bob. Your solution worked
perfectly. As for the regeneration of the random numbers and thus
random words, this was a desired effect.


With much gratitude,

Moe

 

[SM Mister Moe]

JE-

I just saw your post. Thank you very much for your response as well.
I had not considered the possibility of a 401 occurring as a result of
the rounding. I appreciate this insight and your solution works just
as well.

Gratefully,

Moe

 

[SM Mister Moe]

It seems that for the way I am organizing my results that Bob's formul
may be better suited. I am concerned, however, at the possibility of
401 random number as you pointed out. If I changed my random numbe
generating formula to:

=ROUND((RAND()*399 + 1),0)

would that cause the maximum to only be 400?


Thanks,

Mo

 

[Bob Phillips]

Moe,

Why not just use

=INT(RAND()*400)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

It seems that for the way I am organizing my results that Bob's formula
may be better suited. I am concerned, however, at the possibility of a
401 random number as you pointed out. If I changed my random number
generating formula to:

=ROUND((RAND()*399 + 1),0)

would that cause the maximum to only be 400?

It would eliminate the possibility of getting 401 as a result, but the odds
of getting 1 or 400 would be half that of getting any other number between 2
and 399. That is, the odds of getting any number between 2 and 399 would be
1/399, but the odds of getting 1 or 400 would be 1/798. You avoid this by
using INT, as J.E. suggested, rather than ROUND. Adapted to Bob's formula,

=INDEX($G$1:$G$400,A1,1)

where A1 contains the formula =INT(1+400*RAND()) .

 

[Bob Phillips]

.... because you don't want 0

So just add the 1.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Moe,

Why not just use

=INT(RAND()*400)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

Why not just use

=INT(RAND()*400)

With OFFSET, good idea. With INDEX, should generate #REF! errors once out of
400 recalcs on average, and would never give the last word in the list of
400 words. This would happen when REAND() evaluates < 1/400, in which case
the formula above would return 0. Since RAND() (at least through Excel
2002 - unwise to say anything about Excel 2003 yet) always returns a value
strictly less than 1, RAND()*400 would always be strictly less than 400, so
the formula above would never return 400.

 

[SM Mister Moe]

Bob, JE, Harlan-

Thank you for your continued responses. I see the problem that Harla
pointed out about 1 and 400 being half as likely to occur as 2 throug
399 and in the case of using =INT(RAND()*400), an error answer of zer
is possible and 400 could never come up.

The way I have it set up now is as follows:

I have =INT(1+400*RAND()) in the number generation table starting i
A1. The formula is placed in cells forming a 5 x 10 table.

I have =INDEX($G$1:$G$400,B1,1) in the word generation table startin
in A12. Again the formula is placed in a 5 x 10 table. The locatio
is not relevant because I have the formula changing in the prope
order, e.g. B13 has =INDEX($G$1:$G$400,A2,1).

I have my 400 words going straight down in column G from G1 to G400.

Using that setup I have not gotten any #REF! errors and was able to ge
solutions of both 1 and 400. I am curious then if this is the bes
solution and the probability of getting any number is in fact 1 in 400
As I mentioned earlier, I am a relatively novice Excel 2002 user, bu
I am curious about the OFFSET function you mentioned as I am no
familiar with it. Also, while my current results are sufficient and
now understand the INDEX command, it would be very helpful to not hav
repeat random numbers generated. Is it possible to keep repeat rando
numbers and thus no repeat words from occurring?

Thanks for all your continued assistance,

Mo

 

[Bob Phillips]

Moe,

Here is one way that may or may not be good for you.

First, ensure cell F1 is empty and goto Tools>Options and on the Calculation
tab check the Iteration checkbox to stop the Circular Reference message.

Next, type this formula into cell A1
=IF(($F$1="")+AND(A1>0,COUNTIF($A$1:$E$12,A1)=1),A1,INT(1+400*RAND()))
it should show a 0

Copy A1 down to A12, and then copy A1:A12 across to E1:E12.

Finally, put some value in F1, say an 'x', and all the random numbers will
be generated.

They won't change.

To force a re-calculation, clear cell F1, edit cell A1, don't change it,
just edit to reset to 0, copy A1 down to A12, and then copy A1:A12 across to
E1:E12, and re-input F1

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[J.E. McGimpsey]

Take a look here:

Random Integers without repetition
http://www.mcgimpsey.com/excel/randint.html

 

[SM Mister Moe]

Thank you very much. Your suggestions have been most helpful and that
tutorial was very informative.


Thanks,


Moe