#### random select a percentage from a list

``` I am trying to randomly select a percentage from a list of 891 for
assessment interviews.  Do I use Rand ()*.1 for 10 percent of the selection?
```
 0
Utf
4/6/2010 2:35:02 PM
excel.misc 78881 articles. 5 followers.

5 Replies
3108 Views

Similar Articles

[PageSpeed] 5

```=RAND()
gives you a number between 0 and 1

=RAND()*891
will give you a number between 0 and 891

You can round it to the nearest 1 with
=ROUND(RAND()*891,0)

If you only want to look at 10% of the 891, you can use
=ROUND(RAND()*891*0.1,0)
which will give you a number from 0 to 89.

It is not clear exactly what you mean but i think you'll get what you need
from what I have written.
--
Allllen

"Rocetman" wrote:

>  I am trying to randomly select a percentage from a list of 891 for
> assessment interviews.  Do I use Rand ()*.1 for 10 percent of the selection?
```
 0
Utf
4/6/2010 3:58:01 PM
```You can use the built-in Data Analysis / Sampling utility.
--
Jim Cone
Portland, Oregon  USA

"Rocetman" <Rocetman@discussions.microsoft.com>
wrote in message
news:3BEE9A91-8C92-4714-B761-DEEBED9F913A@microsoft.com...
I am trying to randomly select a percentage from a list of 891 for
assessment interviews.  Do I use Rand ()*.1 for 10 percent of the selection?
```
 0
Jim
4/6/2010 4:28:11 PM
```"Rocetman" <Rocetman@discussions.microsoft.com> wrote:
> I am trying to randomly select a percentage from a list of 891 for
> assessment interviews.

If you want the percentage to be a "variable" -- a cell whose value you
provide, consider the following.

Suppose your data is in A2:A892.  And suppose the desired percentage is in
C2, entered in the form 10% or 0.1.  C2 can also be a random percentage,
which can be entered as =ROUND(RAND(),2) for example.

In some out-of-the-way range, say X2:X892, put the formula =RAND() into each
cell.

Then, if you want B2:B892 to contain the random selection of a percentage of
the list in A2:A892, enter the following formula into B2 and copy down
through B892:

=IF(ROW()-ROW(\$B\$2)+1 > \$C\$2*COUNTA(\$A\$2:\$A\$892), "", INDEX(\$A\$2:\$A\$892,
RANK(X2,\$X\$2:\$X\$892)))

Some important notes:

1. You might prefer ROUND(\$C\$2*COUNTA(\$A\$2:\$A\$892), 0).

2. Since RAND() changes every time you edit any cell in the workbook(!), you
might want to put the RAND formulas into some other cells, then
copy-and-paste-special-value into C2 and X2:X892.  There are also other ways
of getting nonvolatile random values.

```
 0
Joe
4/6/2010 4:50:00 PM
``` Joe  wrote  on Tue, 6 Apr 2010 09:50:00 -0700:

> "Rocetman" <Rocetman@discussions.microsoft.com> wrote:
>> I am trying to randomly select a percentage from a list of
>> 891 for assessment interviews.

> If you want the percentage to be a "variable" -- a cell whose value
> you provide, consider the following.

> Suppose your data is in A2:A892.  And suppose the desired percentage
> is in C2, entered in the form 10% or 0.1.  C2 can also be a
> random percentage, which can be entered as =ROUND(RAND(),2)
> for example.

> In some out-of-the-way range, say X2:X892, put the formula =RAND()
> into each cell.

> Then, if you want B2:B892 to contain the random selection of a
> percentage of the list in A2:A892, enter the following formula into
> B2 and copy down through B892:

> =IF(ROW()-ROW(\$B\$2)+1 > \$C\$2*COUNTA(\$A\$2:\$A\$892), "",
> INDEX(\$A\$2:\$A\$892, RANK(X2,\$X\$2:\$X\$892)))

> Some important notes:

> 1. You might prefer ROUND(\$C\$2*COUNTA(\$A\$2:\$A\$892), 0).

You might consider using randbetween(1,891), paste special value, and
then sort.

--

James Silverton
Potomac, Maryland

Email, with obvious alterations: not.jim.silverton.at.verizon.not

```
 0
James
4/6/2010 5:31:21 PM
```"James Silverton" <not.jim.silverton@verizon.net> wrote:
> Joe  wrote  on Tue, 6 Apr 2010 09:50:00 -0700:
>> 1. You might prefer ROUND(\$C\$2*COUNTA(\$A\$2:\$A\$892), 0).
>
> You might consider using randbetween(1,891), paste special value,
> and then sort.

It is unclear what part of my posting you are commenting on.  I assume it is
that last statement, since you truncate the rest of my posting.

Of course, RANDBETWEEN(1,891) is not equivalent to
ROUND(C2*COUNTA(A2:A892),0).  The latter results in a user-specified
percentage (in C2) of 891, whereas the former results in a random
"percentage" over which the use has no control.

However, it is a matter of interpretation of what exactly the OP means by
"randomly select a percentage from a list".

I interpret it to mean "randomly select from a percentage of a list".

You might be assuming it means "select from a random percentage of a list".
But that begs the question:  is the selection itself random (i.e. "randomly
select from a random percentage of a list"), or does it mean "select the
first random percentage of a list"?

If the OP had meant as you might assume, the OP's phrasing would be poor
grammar (misplaced antecedent).  But we've seen much worse, of course.  In
fact, the entire sentence leaves room for wild interpretations, since the OP
does not say what the list is composed of.  I assume it is a list of names.
It could be a list of percentages ;-).

----- original message -----

"James Silverton" <not.jim.silverton@verizon.net> wrote in message
news:%23iJN67a1KHA.4560@TK2MSFTNGP02.phx.gbl...
> Joe  wrote  on Tue, 6 Apr 2010 09:50:00 -0700:
>
>> "Rocetman" <Rocetman@discussions.microsoft.com> wrote:
>>> I am trying to randomly select a percentage from a list of
>>> 891 for assessment interviews.
>
>> If you want the percentage to be a "variable" -- a cell whose value you
>> provide, consider the following.
>
>> Suppose your data is in A2:A892.  And suppose the desired percentage is
>> in C2, entered in the form 10% or 0.1.  C2 can also be a
>> random percentage, which can be entered as =ROUND(RAND(),2)
>> for example.
>
>> In some out-of-the-way range, say X2:X892, put the formula =RAND()
>> into each cell.
>
>> Then, if you want B2:B892 to contain the random selection of a
>> percentage of the list in A2:A892, enter the following formula into
>> B2 and copy down through B892:
>
>> =IF(ROW()-ROW(\$B\$2)+1 > \$C\$2*COUNTA(\$A\$2:\$A\$892), "",
>> INDEX(\$A\$2:\$A\$892, RANK(X2,\$X\$2:\$X\$892)))
>
>> Some important notes:
>
>> 1. You might prefer ROUND(\$C\$2*COUNTA(\$A\$2:\$A\$892), 0).
>
> You might consider using randbetween(1,891), paste special value, and then
> sort.
>
> --
>
> James Silverton
> Potomac, Maryland
>
> Email, with obvious alterations: not.jim.silverton.at.verizon.not

```
 0
Joe
4/6/2010 6:19:09 PM

Similar Artilces:

Merging List w/ a Form
Can anyone assist me with merging a form created in Excel with a database (or list) also created in Excel? I know how to merge an Excel worksheet to be used as a database for Word in the Mail Merge command, but my FORM, with which I am trying to merge my database, was created in Excel and needs to remain under its format (not be converted to Word). I am filtering the information I need from the database to be imported to the Excel Form. Any help would be appreciated. Thank-you. If you're trying to display information from one record in the database, there are sample files here...

Hiding OLD customers in list
I have a customer list (tblCustomers) And an Orders table (tblOrders) I want to have an admin area functionality that prunes (hides from display in any list or report) Customers from the list that have not placed an order in X number of days (Admin can enter the number of days in a text box) Solution does not have to be in days or in a text box. Could be several check boxes (30 days, 10 weeks, 2 years...). I am just looking for the simplest solution for us to limit the Customer list by last order date. Any help here will be appreciated. Thanks in advance If it were me, I would have a ...

I have a team of account people who assign tasks to a production team. I want to create a shared task list that I (and others) can assign tasks to my team that also updates me when progress on the tasks has been made (ex. marked complete). I know I can do this from my personal task list, but is there a way to have a shared task list where items can be assigned and will also be updated through exchange? Public folders maybe...? Using Outlook 2003 on exchange server. Thanks! Chris Set up a shared Mailbox and make Tasks there, Assign them to team members. In that Tasks folder, show t...

Printer driver keeping printed stuff from teh day before to reprint at random
We just moved our shipping operations onto a windows 7(64) platform. This include a USB Eltron 2844 label printer for our UPS operations. Our mainstay application is browser based, and prints UPS labels to this printer by downloading a .EPL file, which is then copied to the printer with a simple batch file. Attempting to print a single label results in printing two to seven DIFFERENT labels, all valid labels, originally printed within the last 24 hours or so, along with AT THE END OF THE PRINTING, the one label we wanted. After trying in vain to determine what was wrong with ...

First row in Selection range (first index of a cell)
Hello, I have a problem with selecting first cel in selection Range or return an index of the first cell in Selection Cell. I have something like this: .................... Range1.Select "and here I want to Select the first range in selection Range1" ............. I there any special function of finding first cell in selection range or returning an index of the first cell?? Thanks for answet Marcin Maybe range1(1).select or range1.cells(1).select or range1.cells(1,1).select mar_male@wp.pl wrote: > > Hello, > I have a problem with selecting first cel in selection...

List of Differences Between Outlook 2000 and Outlook 2003
I need to spend 45 minutes introducing Outlook 2003 to my Outlook 2000 users. Can anyone provide a site with a succinct list (screenshots would help) of differences between Outlook 2000 and Outlook 2003? Many thanks. Tom Are you looking for screenshots of Outlook 2003 or ones comparing OL2000 and 2003? Wouldn't you be a more convincing teacher if used both and took the screenshots yourself? This would help you learn and understand the products better. (Vmware or virtual pc make this really easy and you could show them the differences live. :)) -- Diane Poremsky [MVP - Outlook] ...

Contact List
How come whne I put in a contact in Outlook 2004, and then I add a fax #, it lists the contact twice on my main list? It's because there is no Outlook 2004. The standard behavior for the Outlook Address book is to display all electronic addresses (both fax and e-mail). That behavior cannot be changed. The only way to prevent the display of fax numbers is to store them in a different field or to disguise them (e.g., precede them with an alpha character) so that Outlook won't recognize them as phone numbers. There are also a number of utilities available that can do this for you automat...

grabbing emails and paste into dist list?
Hi there, Okay, here's the scenario in Outlook 2003. I get an email from someone who has included about 50 important people in the cc field. I'd like to take all those email addresses and dump them into a distribution list in one fell swoop. So far, I hit "reply to all" and selected all of those cc email addresses, but I can not paste into a distribution list. How can I do this? Thanks, Gordon ...

Selecting Text Box
Is there a keyboard command that one can use with the mouse to select the text box (frame) rather than trigger the Edit Text mode? When there are many objects on the page, getting the mouse to click on the right frame can be a real problem. But if the cursor is in the right box, some keyboard command (with or without the mouse) should help select the right object. Thanks! ...

Filters, Can't Find Exception List In Rules
I'm trying to set up my filters for junk mail. I want junk mail with certain words in the subject sent to deleted folder. I found the filters.txt file and some of the words are already there. So apparently the filter isn't working. I tried to follow the directions on how to turn them on and they said to choose the exceptions entry in the rules wizard but I have no such choice. I know how to edit the filters.txt file but Outlook is not using it. Can anyone help? The Filters.txt file isn't actively used by Outlook -- it's really just a "readme" type file to sh...

Scrolling Tables list
When I use the scroll bar to move up and down the list of tables or queries etc, the list continues to vacillate after I stop sliding the bar; making it difficult to find the item I want to select. This seems to be worse with my new computer. Are there settings I should adjust. "Crop scout" <Crop scout@discussions.microsoft.com> kirjoitti viestiss�:771C65DD-5D6E-47EF-B987-C797A680FB59@microsoft.com... > When I use the scroll bar to move up and down the list of tables or > queries > etc, the list continues to vacillate after I stop sliding the bar; makin...

SQL select statement question
My dataset has 3 columns: customer id, store location, charges I'd like to write a single select statment that will show each customer id once, with the store location where they have the most charges, and the sum of the charges for that store location. This gives the max charges for each customer, but doesn't give the location: select a.custID, max(new.amt) from smallchg a inner join; (select custID, location, sum(charges) as amt from smallchg group by custID, location) as new ; on a.custID= new.CustID group by a.CustID thanks. joel Try this -- SELECT TOP 1 ...

Creating a e-mail contact list which has "undisclosed recipients"
Can anyone help me with a contact list for my e-mail which does not disclose all recipients names. I would like to send an e-mail regarding company information to several people which answers questions, but I don't want everyone to know who I am addressing it to. You need enter the names under the Bcc option when you address the email. That way, each recipient will only see their own name, but the email will go to everyone. Actually, the recipients won't see their own name in the To field. They will only see what the sender puts there, if anything (some ISPs require that you...

Control Supported Device List for Active Sync
Hi I want to restrict what devices can connect to Active Sync - there is a built in list of supported devices which the service packs keep updating but is there a way to control this list yourself i.e. to only include two devices in the list? I want to control which devices meet the company standard and stop any which don't! More Info: Overview of mobile devices that are supported by Outlook Mobile Access in Exchange Server 2003 http://support.microsoft.com/Default.aspx?kbid=821835 Thanks Clive ...

Drop Down List for Chart
I have grouped my series into several groups. How do I display a drop down list including the groups which will dynamically identify which series to plot on the chart? For example: Group A = Series 1, Series 2, Series 3 Group B = Series 4, Series 5 Group C = Series 6 Drop down list includes values: "Group A", "Group B", "Group C" When Group A is selected within the drop down, Series 1, 2 and 3 are plotted on the chart. Do you mean you have to plot data, and have the chart move week to week? if so, easiest way is to use a hlookup/vlookup "savior1&quo...

hwo to recover personal folders and contact list??
I had office 2003 running on my C: drive and I had a major system crash so I backed up the entire C: drive to D: .. then I formatted and reinstalled windows XP pro and office 2003. can someone tell me where on the D: drive I woudld find the contact list and all my folders from outlook 2003 that I had running before? THanks.. Hi Dave, please look here, which datas are important for outlook http://home.arcor.de/andreas.roeder-privat/English%20Help.htm#18 If you search for the relevant files you could import them into your new Outlook! -- Gestern gings noch, ICH habe nichts gemacht! ;-) Brave...

Customize global adress list column in a new mail
Hi! (I have Outlook 2007 and Exchange 2007). when i create a new mail message, hit the "To" button, my global adress list window appear when column by default: Name, Title, business Phone, Location, E-mail address, Company and Alias. Those column from Active directory of course. My question is: can I add/remove column in this window? For example adding "department" from Active directory? thx Since this is customized by your Exchange Admin, I would start by asking him/her if it is possible. This question can most likely be answered in an Exchange or W...

finding common numbers in large lists
Hello, I have two large lists of numbers (on two seperate worksheets). By large, I mean that one list is 1000+ numbers, and the second list is 3000+ numbers (each number is in its own cell). The numbers are of the form: xxxx.xxx (that is, they are precise up to three places after the decimal) 1) Is there some quick way that I can find the numbers that are common to both lists? 2) Is there a way I can find the numbers in common to both lists using only the whole number portion (diregarding what comes after the decimal of each number)? Thanks! Hi see: http://www.cpearson.com/excel/duplic...

Dynamically change pick lists?
Has anyone found a workaround for the following : dynamically change the choices in one pick list based on the selection in a second, associated pick list. I know this question has been asked before, just wondering if someone found a resolution for it. I haven't tried it yet, but it is my understanding that you can write JavaScript code in the OnChange event for the first picklist that will modify the valid values in the second picklist. HTH, Dave >-----Original Message----- >Has anyone found a workaround for the following : > >dynamically change the choices in one pick...

Changing Default Contacts List for E-mail
I have two separate Contacts lists open. When I go to select names for the e-mail addresses, Outlook always defaults to the wrong list, and I have to manually select the other one. How can I change the default list that shows up when I click on the "to:" header field in an e- mail? Outlook version? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Michael Leu" <anonymous@discussions.microsoft.com> wrote in message news:056501...

Combo On Not In List
Hi, To fulfil some wishes of a user entering a persons name into a combo box, that is not on the list and having three choices i thought of the following, Its not working gets hung up before it starts. User choices are too add the name to the list and open a form, to not do anything and allow the name to populate the field overriding the Not In List and third to just add the name to the list an not open the form From the form call up a function in a module as follows. Private Sub Combo106_NotInList(NewData As String, Response As Integer) ' Allows user to add a new Conta...

Adding a customer to multiple marketing lists
Is there a way to add a customer to multiple marketing lists at once? When I try this by going to Actions, Add to Marketing Lists, it only lets me select one row. You can add an account to one marketing list from the Actions Menu. You will need a plugin which will add an account to many marketing lists. SS "Tara C. Brown" wrote: > Is there a way to add a customer to multiple marketing lists at once? When I > try this by going to Actions, Add to Marketing Lists, it only lets me select > one row. What plugin is necessary to do this? "SS" wrote: > You...

Extra/incorrect names in "To" address drop-down list
After you have sent emails to recipients, Windows Mail "remembers" the names/email addresses. Unfortunately, if you send an email using an incorrectly typed email address it remembers it also. Therefore, when you type the first letter of the recipient you are sending a message, both the incorrect and correct address appears, which can be troublesome if the incorrect address is not apparent. How do you remove the incorrect address from the drop-down? Thanks in advance. "lawdog1881" <lawdog1881@discussions.microsoft.com> wrote in message news:C561E...

Editing the investment list
Anyone who can answer this, your answer will be most appreciated. When you create transactions, each investment ultimately ends up from a list that you can choose. How can you edit this list? I have two similar entries, one of which I want to delete, and online help is not so helpful :-( Thanks, Jon Portfolio|Work with investments|Choose a specific investment??? "Jonathan Held" <jsheld@hotmail.com> wrote in message news:3f733afa\$1@news.microsoft.com... > Anyone who can answer this, your answer will be most appreciated. When you > create transactions, each investm...

Only one GC is listed in the Directory Access Tab
I am in a mulity domain multiy exchange server enviroment I am in my own administrative group. When I create the RUS and go back and modify it I have the option to select any DC in my AD site, but when I look on the directory access tab, only one server is listed. I have confirmed that Discovery Servers is checked. I also have confirmed that all domain controllers are selected from the drop down. I turned on DCAccess logining and all the servers are listed. Can anyone point me in the right direction on what to troubleshoot next. Thanks DNS is what you need to trouble shoot, Clear the DN...