#1   Report Post  
Nerine
 
Posts: n/a
Default 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)
  #2   Report Post  
Biff
 
Posts: n/a
Default

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

  #3   Report Post  
AV
 
Posts: n/a
Default

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

AV


  #4   Report Post  
Alex Delamain
 
Posts: n/a
Default


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Seed numbers for random number generation, uniform distribution darebo Excel Discussion (Misc queries) 3 April 21st 23 09:02 PM
How can I get Positive values only from the random number generat. Markw3700 Excel Discussion (Misc queries) 1 January 21st 05 01:37 AM
Generating Correlated Random Values in Excel Randy Excel Discussion (Misc queries) 2 January 16th 05 10:50 PM
Setting up a random list from long list of names ? yorkshire exile Excel Discussion (Misc queries) 4 January 6th 05 02:44 PM
Using autofilter, how do I enter several random postcodesinto 'co. novice Excel Discussion (Misc queries) 5 December 4th 04 11:42 PM


All times are GMT +1. The time now is 12:36 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"