generate a random number and use if function to generate new data

[Guest]

I am trying to use the rand function to generate an interger between 0-3.
Depending on the result, I'm attempting to assign a text value i.e. if
rand()*3=1,"spades","clubs". The process will work only once. When I use F9
to generate a new random number, the text value remains the same regardless
of the new result for rand()*3. How do I get Excel to generate a new text
value, based on the new random number?

 

[Max]

Perhaps an easier way is to use randbetween within say, a vlookup with a
"standalone" table array, something like:

In any cell, say B2:
=VLOOKUP(randbetween(0,3),{0,"spades";1,"clubs";2,"diamonds";3,"hearts"},2,0
)

 

[Max]

Note that RANDBETWEEN requires the Analysis Toolpak (ATP)
to be installed and activated.

Check the "Analysis Toolpak" box (via Tools > Add-Ins)

Chip Pearson's page has details on the ATP at:
http://www.cpearson.com/excel/ATP.htm


---

 

[Peo Sjoblom]

Try

=LOOKUP(RAND()*3,{0,1,2},{"spades";"clubs";"hearts"})

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey

 

[Guest]

I am trying to use the rand function to generate an interger
between 0-3.
=int(4*rand())

Depending on the result, I'm attempting to assign a text
value i.e. if rand()*3=1,"spades","clubs".

=index({"hearts","spades","diamonds","clubs"},1+int(4*rand()))

Note: You do not really need int(...) in this context.
1+4*rand() will suffice.