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

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!

How about this one, where there's no need for it:

=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!
>
>How about this one, where there's no need for it:
>
>=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
Reply:

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

emailing spreadsheet within excel
I have a user that has sent her spreadsheet as an attachment using the Send To Function. We use Outlook. Now, it seems as if the Send To option has lost its settings and is now trying to set up an Internet account instead of using the Microsoft Exchange Settings. Anyone know how to reset the Send To settings in Excel? Thanks! ...

generate list of non-entries within dates
I use Excel (2007) to record activity and support for about 100 current online learners and I have to provide an activity summary sheet every two weeks. The list of learners is in one sheet and are marked "current", agreed break" or "completed" in an adjacent column. The total list is about 1000 and grows by about 10 each week. The activity log is another sheet of the same workbook. Assuming that all activity is logged in this way I need to generate a list (sheet) of those current learners who have not submitted any recent work and are therefore "inacti...

Adding a formula to the same cell (H5) on every tab
I have an inventory spreadsheet with 125 tabs. The tabs are numbered 1 through 125. The are identical except for the data below the column headings. If I wanted to put a formula in H5 on every tab, can it be done other than manually opening every tab and typing it? One additional question: If I add a Summary Tab, how could I show the value of a specific cell on each tab without manually entering it? I show the formula I'm using bring B3 to the summary for every tab: A B 1 Unit Value 2 1 ='1'!B3 3 2 ='2'!B3 4 3 ='3'!B3 5 4 ='4'!B3 6 5 ='5'!B3 7...

Can't access below toolbar.
I've used Windows Live mail successfully for several months but now I cannot access anything below the File, Edit, View etc bar. My mouse doesn't get a response from any button below that line. Help! This is a multi-part message in MIME format. ------=_NextPart_000_0037_01CB179F.F06F5C60 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi, Trevecchi. That=E2=80=99s a weird one that I haven=E2=80=99t heard before. But = that bar that starts with File | Edit | View... is the Menu Bar, not the = Toolbar. I hate to be...