#### How can I set up the random selection of a cell from within a ran.

```I want Excel to randomly select one cell from a range of cells, but is it
possible?  This would be the equivalent of drawing one slip of paper from
several hundred.
```
 0
nybbac (1)
1/1/2005 2:47:03 AM
excel.misc 78881 articles. 5 followers.

5 Replies
633 Views

Similar Articles

[PageSpeed] 19

```An uncomplicated way to do this is to simply add a column filled with the
RAND() function next to your column of choices.
Select both columns, and sort on the Rand() column.
Pick the one at the top, or bottom, as your random selection.

A little more complicated procedure would be to use a formula, referring to
this combination of Rand() and your list, and let this formula randomly pick
an item.
Place your list in an "out of the way" section of your sheet, say Y1:Y10.
In Z1, enter:
=RAND()
and drag down to copy to Z10.

Then, enter this formula wherever you wish:

=INDEX(Y1:Y10,RANK(Z1:Z10,Z1:Z10))

Enter this formula as is, if you're going to use it in Row 1 to 10.
If beyond those rows, you'll have to enter it as an array formula using CSE,
<Ctrl> <Shift> <Enter>.

Now, every time the sheet calculates, or you hit <F9>, you'll get a NEW
random selection.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"nybbac" <nybbac@discussions.microsoft.com> wrote in message
news:96EFFA79-2480-4871-847D-F937B0A73D33@microsoft.com...
> I want Excel to randomly select one cell from a range of cells, but is it
> possible?  This would be the equivalent of drawing one slip of paper from
> several hundred.

```
 0
ragdyer1 (4060)
1/1/2005 3:55:18 AM
```Hi, nybbac;
Here's another way, if your range is in one column:
Insert a column to the left of it and fill that column with a numerical
series.
If 1 is the first number in that series, and 400 is the last, then enter
this formula into an empty cell: =RANDBETWEEN(1,400)
If that formula is in, say C1, and your range (including the number series)
is in A1:B400, then enter this formula into another empty cell:
=VLOOKUP(C1,A1:B400,2,FALSE)
RANDBETWEEN will select a random number in your series, and VLOOKUP will
return the data in the cell next to that number. As in the other method, F9
will recalculate.
I wonder if RANDBETWEEN will give me better luck with the lottery. :)
Regards,
IanRoy

"nybbac" wrote:

> I want Excel to randomly select one cell from a range of cells, but is it
> possible?  This would be the equivalent of drawing one slip of paper from
> several hundred.
```
 0
IanRoy (172)
1/1/2005 4:27:01 AM
```Hi!

=INDEX(A1:A400,RANDBETWEEN(1,400))

No need for helper columns or a lookup!

Biff

>-----Original Message-----
>Hi, nybbac;
>Here's another way, if your range is in one column:
>Insert a column to the left of it and fill that column
with a numerical
>series.
>If 1 is the first number in that series, and 400 is the
last, then enter
>this formula into an empty cell: =RANDBETWEEN(1,400)
>If that formula is in, say C1, and your range (including
the number series)
>is in A1:B400, then enter this formula into another empty
cell:
>=VLOOKUP(C1,A1:B400,2,FALSE)
>RANDBETWEEN will select a random number in your series,
and VLOOKUP will
>return the data in the cell next to that number. As in
the other method, F9
>will recalculate.
>I wonder if RANDBETWEEN will give me better luck with the
lottery. :)
>Regards,
>IanRoy
>
>"nybbac" wrote:
>
>> I want Excel to randomly select one cell from a range
of cells, but is it
>> possible?  This would be the equivalent of drawing one
slip of paper from
>> several hundred.
>.
>
```
 0
biffinpitt (3172)
1/1/2005 4:47:49 AM
```Ahhh! Yes ... BUT ... There is the need for the Analysis ToolPak!

=INDEX(A1:A400,RAND()*400+1)
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Biff" <biffinpitt@comcast.net> wrote in message
news:0c5901c4efbd\$0b824280\$a401280a@phx.gbl...
> Hi!
>
> =INDEX(A1:A400,RANDBETWEEN(1,400))
>
> No need for helper columns or a lookup!
>
> Biff
>
> >-----Original Message-----
> >Hi, nybbac;
> >Here's another way, if your range is in one column:
> >Insert a column to the left of it and fill that column
> with a numerical
> >series.
> >If 1 is the first number in that series, and 400 is the
> last, then enter
> >this formula into an empty cell: =RANDBETWEEN(1,400)
> >If that formula is in, say C1, and your range (including
> the number series)
> >is in A1:B400, then enter this formula into another empty
> cell:
> >=VLOOKUP(C1,A1:B400,2,FALSE)
> >RANDBETWEEN will select a random number in your series,
> and VLOOKUP will
> >return the data in the cell next to that number. As in
> the other method, F9
> >will recalculate.
> >I wonder if RANDBETWEEN will give me better luck with the
> lottery. :)
> >Regards,
> >IanRoy
> >
> >"nybbac" wrote:
> >
> >> I want Excel to randomly select one cell from a range
> of cells, but is it
> >> possible?  This would be the equivalent of drawing one
> slip of paper from
> >> several hundred.
> >.
> >

```
 0
ragdyer1 (4060)
1/1/2005 5:21:38 AM
```Yeah, that'll work!

Biff

>-----Original Message-----
>Ahhh! Yes ... BUT ... There is the need for the Analysis
ToolPak!
>
>
>=INDEX(A1:A400,RAND()*400+1)
>--
>HTH,
>
>RD
>
>----------------------------------------------------------
-----------------
>Please keep all correspondence within the NewsGroup, so
all may benefit !
>----------------------------------------------------------
-----------------
>
>"Biff" <biffinpitt@comcast.net> wrote in message
>news:0c5901c4efbd\$0b824280\$a401280a@phx.gbl...
>> Hi!
>>
>> =INDEX(A1:A400,RANDBETWEEN(1,400))
>>
>> No need for helper columns or a lookup!
>>
>> Biff
>>
>> >-----Original Message-----
>> >Hi, nybbac;
>> >Here's another way, if your range is in one column:
>> >Insert a column to the left of it and fill that column
>> with a numerical
>> >series.
>> >If 1 is the first number in that series, and 400 is the
>> last, then enter
>> >this formula into an empty cell: =RANDBETWEEN(1,400)
>> >If that formula is in, say C1, and your range
(including
>> the number series)
>> >is in A1:B400, then enter this formula into another
empty
>> cell:
>> >=VLOOKUP(C1,A1:B400,2,FALSE)
>> >RANDBETWEEN will select a random number in your series,
>> and VLOOKUP will
>> >return the data in the cell next to that number. As in
>> the other method, F9
>> >will recalculate.
>> >I wonder if RANDBETWEEN will give me better luck with
the
>> lottery. :)
>> >Regards,
>> >IanRoy
>> >
>> >"nybbac" wrote:
>> >
>> >> I want Excel to randomly select one cell from a range
>> of cells, but is it
>> >> possible?  This would be the equivalent of drawing
one
>> slip of paper from
>> >> several hundred.
>> >.
>> >
>
>.
>
```
 0
biffinpitt (3172)
1/1/2005 5:39:18 AM

Similar Artilces:

Can I lock the OE6 UI?
Is it possible to lock the Outlook Express UI? I am supporting a novice/elderly user who is using OE6 on WinXP SP3. This user has a habit of accidentally closing toolbars or the folder view from time to time causing considerable confusion and a support call. To prevent this problem, I would like to lock-down OE6 somehow so that once the UI (toolbars, views, menu, etc.) is arranged nicely, it cannot be changed without administrator permission. Or failing that, can I create a default UI view for OE6 that can be easily restored (i.e. "click here") when things start looking ...

Setting Default Domain for Logon
I am running OWA for Exchange 2003 and I have set the default domain for the default web site to mywindows2000domain.com. When I logon to OWA this doesn't seem to be working because when the user goes to logoff they get a username and pw prompt. If they login with mywindows2000domain\username when they login it works fine. I would like to not use the mywindows2000domain\username format. Any idea? Cheers! try using mywindows2000domain (netbious name of the domain) instead of mywindows2000domain.com (fqdn of the domain) -rp- "Rio" <anonymous@discussions.microsoft...

Recurring Appointments Set by Deleted Users
Hello.. I'm terribly new to Exchange. *red flag!* *red flag!* We have a recurring appointment that we need to add users to, however, we cannot change the appointment because the user that organized it was deleted long ago. How can we go about changing these appointments? Thanks in advance! Re-create it. On 30 Dec 2004 10:35:48 -0800, "Basic" <cbromley@gmail.com> wrote: >Hello.. > >I'm terribly new to Exchange. *red flag!* *red flag!* We have a >recurring appointment that we need to add users to, however, we cannot >change the appointment becau...

How do I set up inches per Minute/Second in Excel
I need to know how to take a known inch per Minute and divide by a known measurement and displayed as minutes and seconds. The answer of the equation (minutes and seconds) is where I'm having trouble. Actually, you need to divide your measured length by the known rate (inches per minute). This gives you the number of minutes, but to display using m:ss format you need to divide by 1440, the number of minutes per day (since Excel counts time as the fraction of a day). So put your measurement in A2, your rate in B2, and this formula into C2: =A2/B2/1440 Then format C2 using a custom...

Why does the right side of my paper not print (within margin) Pub
I've had this problem for awhile now, but the current document is a postcard I'm printing 4 to a page with a border around each one. The whole thing prints out just fine except for the right side of the page, which happens to be the whole side of the border. It is well within the margins. I've checked all the settings within Publisher 2003 and my printer program (Dell Photo AIO) that I can find. Thanks! ~Renee'~ Renee'''' wrote: > I've had this problem for awhile now, but the current document is a postcard > I'm printing 4 to a page with a...

Can I make a database in Word with Excel Data
I know this may be a "DUH" question, but... I am making a list of area dentists that I have copied and pasted of of the Dex website into Excel. I am wondering if I can take this inf and make a database in Word for printing address labels. Does anyon know if this can be done, and HOW?:confused -- Myn77 ----------------------------------------------------------------------- Myn777's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2958 View this thread: http://www.excelforum.com/showthread.php?threadid=49283 For help on Word mail merge using Excel as the...

How to see calculation and heading in same cell.
I would like the cell to perform a calculation and then display the answer as will as a heading. In other words the answer and the heading will appear in the same cell. Perhaps you mean something like ="Heading Name: "&A1+B1 ******************* ~Anne Troy www.OfficeArticles.com "Jeracho" <Jeracho@discussions.microsoft.com> wrote in message news:F34BACB9-E2DA-448A-924D-46A475F98F91@microsoft.com... > I would like the cell to perform a calculation and then display the answer as > will as a heading. In other words the answer and the heading will appear in...

How to set Recipient Limits option under Message Delivery for certain users
Dear All, Quick Question: under Global Settings --> Message Delivery , under the properites of Message Delivery there is an option called "Recipient Limits" which will limit the number of recipients per single email, the defaul number is 5000 recipient per single email. now, if i applied this settings and i mentioned 30 recipients, these settings will be applied to all the users under my exchange server, becasue it is under the Global Settings of Exchange. Now, how can i exclude some VIP users from such settings ? i don't want this Global Settings for recipient limits...

can't delete mailbox #2
I deleted a user account in AD but when I go to Exchange system manager I can't delete the mailbox. What is the deal? Wait until AD replicates then you'll be able to purge the mailboxes with the red Xs "Brandon Baker" <brandon@discussions.microsoft.com> wrote in message news:%23fkHzmOMGHA.1180@TK2MSFTNGP09.phx.gbl... >I deleted a user account in AD but when I go to Exchange system manager I >can't delete the mailbox. What is the deal? > Right click on Mailboxes in ESM and select Run Cleanup Agent. -- John Oliver, Jr MCSE, MCT, CCNA Exchange M...

can't get scrollbar to come up -- CScrollView
I am new to MFC and I am trying to get a simple app working with a scrollable view. I have put in the call to SetScrollSizes in the OnInitialUpdate method. I'm still not quite sure what sizeTotal is supposed to do. I just gave it a value of 5 for the y for now, which I think I want to set to the number of lines of text I have to display. And then I set y to 50. int numLines = 5; CSize sizeTotal; sizeTotal.cy = numLines; sizeTotal.cx = 50; SetScrollSizes( MM_TEXT, sizeTotal); In the OnDraw method, I put in code to draw my text on the view using the device context. The text get...

Can't receive e-mails on windows mail
I have been trying to access e-mails and getting this message.? Any Help would be great. Thanks. I have re-done all the settings as per their website severall times but with no luck. Running Vista. Account: 'pop3.virginmedia.com', Server: 'pop3.virginmedia.com', Protocol: POP3, Server Response: '-ERR not supported 3pf7420781ewy.51', Port: 995, Secure(SSL): Yes, Server Error: 0x800CCC90, Error Number: 0x800CCC18 Follow the instructions in "How to use POP in Virgin Media Mail" at: http://help.virginmedia.com/system/selfservice.controller?...

Newbie: can't get a calculated value on the form?
I have a table with numbers and a form that shows the numbers. I have a query that takes one of the numbers and mulitplies it. I put a text box on the form from the query result field, but I get a "#Name" error instead of the result. When I run the query, I get the correct result. Help, please? Ed "Ed from AZ" <prof_ofwhat@yahoo.com> wrote in message news:d1e7d27c-11d9-4696-8d19-4c5fdd9dbb89@d70g2000hsb.googlegroups.com... >I have a table with numbers and a form that shows the numbers. I have > a query that takes one of the numbers and mulitplies it. I p...

How can I add numbers but ignore the minus signs?
How can I add numbers but ignore the minus signs? In the following, the answer I'm after is 17 A1 -10 A2 5 A3 -2 =SUMPRODUCT(ABS(A1:A3)) Gord Dibben MS Excel MVP On Thu, 07 Jun 2007 04:19:52 GMT, Invalid <invalid@invalid.comINVALID> wrote: >How can I add numbers but ignore the minus signs? In the following, the >answer I'm after is 17 > >A1 -10 >A2 5 >A3 -2 > > Gord Dibben <gorddibbATshawDOTca> wrote in news:1o2f63pa84r8o6tdf65sa9ptpbv0nlt7u6@4ax.com: Thank you Gord. ----- > =SUMPRODUCT(ABS(A1:A3)) > > > Gord Dibben M...

How can I forward an email as attachment in 2003
Hi Could anybody please tell me how to send emails as attachments in Outlook 2003? Thank you, Paul -- Paulobrien29 Make a new email and then use Insert Item to insert the email you want to send. Regards Judy Gleeson MVP Outlook www.judygleeson.com www.deskdoctors.com Are you sick of bad email practice? Get a copy of my paper "Implementing Email Policy" from the Desk Doctors website. "Paulobrien29" <Paulobrien29.4b477ff@outlookbanter.com> wrote in message news:Paulobrien29.4b477ff@outlookbanter.com... > > Hi > > Could anybody please tell m...

How can I Enable a Check Box based on another fields value?
Hi There, I have a form with a disabled check box. I need to enable it when a certain value ("approved") is selected from a combo box. When I am in Form Design View and I have the Check Box selected the Conditional Formatting menu item on the Format menu is greyed out. I am using Access 2003. Can you tell me what I need to do to make this work? Many thanks, David As you've discovered, Conditional Formatting isn't availabe to checkboxes. Try this: Private Sub Form_Current() If Me.YourComboBox = "Approved" Then YourCheckBox.Enabled = True Else YourChe...

Looking for someone who can integrate CRM with GP using SCRIBE Integration tool
Hi, Is anyone out there in Sydney area who would like to help us in doing integration between CRM 4 and GP 10 through SCRIBE integration tool for a fee? We are looking for someone who has already done integration between these two applications using SCRIBE. You will have to do bit of customisations in the SCRIBE standard templates. If anyone available, you can contact me through my email badri1203@gmail.com Badri ...

data validation
Is it possible to create a similar function like data validation, from which I can select multiple options? (Background: I need a list of many names, from which I case by case can select). Not really. Maybe you could use a listbox. I added a listbox from the Forms toolbar to a worksheet. I called it "List Box 1". I rightclicked on that listbox and chose Format Control. On the Control tab, I chose Selection Type of Multi. I also pointed at the range that held the values that go in that listbox. Then I added a button from the Forms toolbar (I click the button when I'm d...

Coping part of a cell content into a seperate cell
Hi I have two cells, one containing first and middle name and another one with surname. I want to combine the first name and surname into a separate cell, can you advise how I can just copy the first name and miss out the middle name please?? Thanks Caz Hi, I assume that the midle name is separated by a space from the first name and is in column A and the last name in column B =TRIM(LEFT(A2,FIND(" ",a2)-1))&" "&B2 "Caz H" wrote: > Hi > I have two cells, one containing first and middle name and another one with &g...

Ol 2000 email settings
If I configured my outlook 2000 using co-operate or workgroup mail service, with 1 exchange mail account and 2 pop email account from external email service ( like attglobal email etc ). Am I allow to select from which email account I can send from? how to if is yes? Thanks for advice. OL2000 ...

Free shipping for selected customers
Our Store Ops system is setup to always charge shipping on all orders for all customers (the "Automatic shipping" option in Store Ops configuration is checked). For 99% of our customers, this is correct. However, there are a select group of employees who work in our warehouse. They do not need the garments shipped to them, so they should not automatically be charged shipping. There are also some high-end customers who we normally provide free shipping as a courtesy. Our system is also setup to received orders from a website (using a custom web interface). It can feed the cu...

How can I do an hourly salary calculation in Excel
How can i do a simple hours (and minutes) x hourly rate calculation and have it display the correct amount in local currency If you are using excel time formats, assume a time value is in A2 and you want to multiply with an hourly rate =A2*24*rate format as currency -- Regards, Peo Sjoblom (No private emails please) "AndyM" <AndyM@discussions.microsoft.com> wrote in message news:4EE0E905-3139-4B70-8119-03A0B2FD4575@microsoft.com... > How can i do a simple hours (and minutes) x hourly rate calculation and > have > it display the correct amount in local cur...