Random

[Nerine]

How do I create a RAND or RANDBETWEEN function (or similar) to display a
random value from a group of cells? ie. =randbetween(a5:a10)

 

[Biff]

Hi!

Here's one way if you're only using a "small" range of
cells:

=INDEX(A5:A10,MATCH(RANDBETWEEN(1,6),{1,2,3,4,5,6},0))

Note that RANDBETWEEN is a volatile function and the value
returned will change every time the wb calculates.

Biff

 

[AV]

=INDEX(A5:A10,INT(RAND()*(7-1)+1))

AV

 

[Alex Delamain]

If you have a large range to look in (e.g. for a prize draw) then pu
the data to be returned in column B with column A numbering 1 upwards

The following formula will check how many rows in column A have dat
and generate a random integer number between 1 and that number. It wil
then look up that number in the table and return the corresponding valu
or tect in column B. The advantage is that as rows are added or delete
the formula will adjust to match it.

=VLOOKUP(RANDBETWEEN(A1,MAX(A:A)),A:B,2