Random Item

[capnsean]

I -have- searched for a previous post regarding this problem, but came
up with nothing all that useful.

Problem: I need to generate a random item from a list.

I.E.: A1 = alpha
A2 = beta
A3 = gamma
etc.

B1 = RANDOM choice of alpha, beta, or gamma


Any help?

 

[Max]

Another option, placed in any cell:
=INDEX({"alpha";"beta";"gamma"},randbetween(1,3))

(adjust the "3" to be equal to the number of items)

Press F9 to regenerate

Note that Randbetween requires the Analysis Toolpak be installed and enabled
(via Tools > Add-Ins)

 

[Max]

If the items are listed within a defined range named "Items",
an alternative to use (in any cell) would be:
=INDEX(Items,randbetween(1,COUNTA(Items)))

---

 

[Tom Ogilvy]

RANDBETWEEN requires the analysis tookpak be installed. You can just use
the built in rand


= OFFSET(A1,Trunc(RAND()*3),0)

=Index(A1:A3,Trunc(rand()*CountA(A1:A3)+1),1)

 

[capnsean]

sorry to be a pain, but how does one define a range?

 

[capnsean]

I defined the group, and tried this

=INDEX(Items,randbetween(1,COUNTA(Items)))

function, to no avail...
I'm getting the ol' #Name?

 

[capnsean]

Nevermind - issue resolved

Muchas gracias

 

[Guest]

In B1 enter =RAND() and copy down to B3.

Then sort columns A&B by B; this will "shuffle" the order of the items in A.

Pick the first item in column A

 

[Max]

Glad to hear that !