Random
 

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)

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

-----Original Message-----
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)
.

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

AV

If you have a large range to look in (e.g. for a prize draw) then put
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 data
and generate a random integer number between 1 and that number. It will
then look up that number in the table and return the corresponding value
or tect in column B. The advantage is that as rows are added or deleted
the formula will adjust to match it.

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


--
Alex Delamain
------------------------------------------------------------------------
Alex Delamain's Profile: http://www.excelforum.com/member.php...o&userid=11273
View this thread: http://www.excelforum.com/showthread...hreadid=274095