Random Distribution

I need to randomly order numbers from a list or range of consecutive whole
numbers, such that each number is listed only once.  Can this be done in
excel using functions, formulas or whatever?  I want to incorporate it into
a spreadsheet with a macro to automatically generate the list and use it to
randomly designate selected entries.
0
forum1 (79)
4/28/2005 8:50:10 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
635 Views

Similar Articles

[PageSpeed] 34


"Rick via OfficeKB.com" wrote:

> I need to randomly order numbers from a list or range of consecutive whole
> numbers, such that each number is listed only once.  Can this be done in
> excel using functions, formulas or whatever?  I want to incorporate it into
> a spreadsheet with a macro to automatically generate the list and use it to
> randomly designate selected entries.
> 

Rick

If your numbers are from 1 to n try the Rank function with rand(). Say F2:F5 
have the function Rand(). In G2 enter =RANK(F2,$F$2:$F$5,0) and copy down.

Regards
Peter


0
4/28/2005 9:46:06 PM
This solution doesn't seem to work - I can get it to produce a list of
random whole numbers ranging from 1 to 50 using the formula =rand()*(50-1)
+1, then rounding that to 0 decimals (if I just use rand() as suggested it
produces a list ranging between 0 and 1) but any given number may be
repeated multiple times and all numbers are not represented.  Then
everytime I apply the next step it recalculates the random list.  For
example, when I then enter the suggested formula in the adjacent column it
recalculates the random list.  When I then copy the formula in the first
cell to the remaining cells in that column it again recalculateds the
random list again.  When I then highilght the adjacent column and
copy/paste values (to remove the formula so I can sort in order) it again
recalculates the ramdom list.  When I sort the adjacent column, which also
recalculates the list, it becomes apparent that all numbers in the range
are not represented and some are represented multiple times.  Short version
- it doesn't work (unless I'm just not doing it right).

-- 
Message posted via http://www.officekb.com
0
forum (466)
4/29/2005 12:44:32 PM
Rick
To get random whole numbers 1 to 50 use
a1> =rand()
b1> =rank(a1,A$1:A$50,0)+countif(A$1:A1,A1)
c1> =rank(B1,B$1:B$50),0)

copy A1:C1 to A1:C50
Column C will give you distinct numbers from 1 to 50.

To avoid recalculation you have to copy C1:C50 and paste as values
somewhere.
Bob


On Fri, 29 Apr 2005 12:44:32 GMT, "Rick via OfficeKB.com"
<forum@OfficeKB.com> wrote:

>This solution doesn't seem to work - I can get it to produce a list of
>random whole numbers ranging from 1 to 50 using the formula =rand()*(50-1)
>+1, then rounding that to 0 decimals (if I just use rand() as suggested it
>produces a list ranging between 0 and 1) but any given number may be
>repeated multiple times and all numbers are not represented.  Then
>everytime I apply the next step it recalculates the random list.  For
>example, when I then enter the suggested formula in the adjacent column it
>recalculates the random list.  When I then copy the formula in the first
>cell to the remaining cells in that column it again recalculateds the
>random list again.  When I then highilght the adjacent column and
>copy/paste values (to remove the formula so I can sort in order) it again
>recalculates the ramdom list.  When I sort the adjacent column, which also
>recalculates the list, it becomes apparent that all numbers in the range
>are not represented and some are represented multiple times.  Short version
>- it doesn't work (unless I'm just not doing it right).

0
4/29/2005 6:53:42 PM
Reply:

Similar Artilces:

Random
How to get random numbers random(0xff) ? int RandomNumber = (rand() * 0xff) / RAND_MAX; -- Bjarne Nielsen "]GHO[" <yousefk@taux01.nsc.com> skrev i en meddelelse news:0b6a01c3a84c$5733a890$a501280a@phx.gbl... > How to get random numbers random(0xff) ? rnd()%0x100; or rnd() & 0xff; Ruben On Tue, 11 Nov 2003 04:07:13 -0800, "]GHO[" <yousefk@taux01.nsc.com> wrote: >How to get random numbers random(0xff) ? ...

Random Numbers not Random
When I create random numbers for an application with the following code, they do not appear to be truely random. Me.RandomNumber = Int(9 * Rnd + 1) ' Random Number 1-9 If I have two users setting side by side, they frequently get the same results when they start up Am I missing somthing? Any help would be appreciated -- Frank Wagner fwagner111@aol.com Frank, Have you tried a Randomize statement just before the RND? That should do it. -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccsolutions/index.html "Find a job tha...

Exchange server randomly asking random users to Authenticate randomly...
Has anyone had a problem like this where a user is in their Exchange mailbox using Outlook 2003 and the server prompts them for their credentials? If they hit cancel and exit out of Outlook and run it again it lets them into their mailbox. Thank you, Benjamin Pls make sure the outlook 2003 Client does not using Cache mode If is ,pls clearn this option -- Jammy "Benjamin Chan" <bchan[pleasenospam]@controlproductsinc.com> ���g��l��s�D :#BxdizrtEHA.3972@TK2MSFTNGP15.phx.gbl... > Has anyone had a problem like this where a user is in their Exchange mailbox > using Outlo...

Re: Export Distribution List Content
Please see "IMI Distribution List Content Exporter" - it is part of IMI GAL Exporter The URL is: http://www.imibo.com/imidev/Exchange/imige.htm We uses it without any problems more that 6 years.... "Vale" <Vale@discussions.microsoft.com> wrote in message news:......9AF091@microsoft.com... > I have in Echange Server 2000 about 20 Distribution List. > How can I export each Distribution List (*.txt or *.csv) singularly ? > Thanks ...

Exchange 2003 Permissions on Distribution groups
Is it possible to lock down the use of a distribution group? Another words, can you allow or disallow a particular user or group the ability to use/view a specific distribution list? For example, we only want to allow certain executives the right to use a distribution group that would email the entire company. Thanks! Hi Plee, If you open the properties of distribution group, on the exchange general tab you have some message restriction settings where you can set the group to accept message from everyone (the default), accept messages only from, or reject messages from. You can add ind...

Standardise & distribute Outlook toolbars
Hi. I want to provide the same 'look&feel' of Outlook, to all the users in my organisation. How can I customise the toolbars and then distribute them to all Outlook clients? I also don't want the users to be able to further customise them. I have tried the tools in the Office Resource Kit, but haven't found any settings that apply to toolbars. Any ideas? Using Outlook 2003 Thanks Toolbar customizations are held in a file called Outcmd.dat. So, one approach would be to set up the toolbars on one machine, then distribute the resulting file to other users. I haven't...

Mail sent to Distribution list fails
I have recently seen a problem on my exchange server. Users will send mail to a distribution list and the mail appears in their sent items but never gets delivered. I have been in the message tracking and it shows the mail is sent all the way up to the point that it delivers to the message journaling mailbox we have set up. Oddly enough; the messages do not appear in the message journaling mailbox either. Any ideas? who belongs to the DL? are those entries valid mailbox-enabled users? -- Susan Conkey [MVP] "vanilla" <espaul@rscquality.com> wrote in message news:1161714...

Generating Correlated Random Values in Excel
Hi Mike, Thanks so much for your advice. I think that I have it figured out, but can you confirm this for me? I can't afford to screw this up. If mean(inflation) = .031 stdev(inflation) = .047 mean(stock return) = .106 stdev(stock return) = .204 Column Headers: A B C D 1 Z1 Z2 Inflation Stock Return Data Generation Formulas A2 =NORMINV(RAND(),0,1) B2 =NORMINV(RAND(),0,1) C2 =0.031+0.047*A2 D2 =0.106+0.204*(A2*rho+B2*(1-rho^2)^0.5) Are these formulas correct to generate the random data? Particularly cell D2. Does this give me a complete value that t...

Send on behalf
Hello, Some of our Entourage users want to send email on behalf of a distribution group, AD permission has been set and it work just fine with Outlook 2007. Once we add the distribution group in the delegated accounts in entourage 2008, it appears as (unconnected) in the left frame and user can't send emails. We can't choose the distribution group in the "from" but email get stuck in the mailbox. Exchange 2007 sp1 - Rollup 8 Macosx 10.x Entourage Web Edition Thanks for your feedback ...

random pages
I have developed pages that randomly define a set of normal lung function data based on gender, age and height. I have applied formulas to this data simulate 5 classic lung function abnormalities (normal, restrictive, obstructive, mixed and early obstructive.) Currently the sheets that define the abnormal are named by one of the above lung abnormalities. I would like to present data from random pages in a work book to students and rather than have the pages named by the abnormality be named a simulated patient name. My questions. Can I have the name of a sheet set to equal data from to ...

Random Distribution
I need to randomly order numbers from a list or range of consecutive whole numbers, such that each number is listed only once. Can this be done in excel using functions, formulas or whatever? I want to incorporate it into a spreadsheet with a macro to automatically generate the list and use it to randomly designate selected entries. "Rick via OfficeKB.com" wrote: > I need to randomly order numbers from a list or range of consecutive whole > numbers, such that each number is listed only once. Can this be done in > excel using functions, formulas or whatever? I want to...

distribution lists not working after change to Exch 2k3 native mod
My exch 2k3 server is not delivering mail from users who are using pre-difined DL's that were created in exch 5.5 . These email(s) are being caught in the queue. When I click on this particular "internet mail SMTP connector" I receive a message in the lower left "additional queue information" that states "unable to bind to the destination server in DNS. Any help is appreciated Supplemental. These are internal DL's that do not require routing to external DNS server. I can resolve all external and internal DNS names from this exchange server. "Zart th...

Restrict multiple distribution groups...
Does anyone know of a way to do a mass distribution group edit to restrict who can send to them? I've tried ADModify.net with no success as the restriction section is not included. BTW - we're running E2k3 Ent. TIA, This is something that is scriptable, but it isn't easy. Possibly http://www.joeware.net might have something that could help you with this. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "kage13" <kage13@discussions.microsoft.com> wrote in message news:42A23351-E46C-481A-AA97-7A4CF6C53E65@microsoft....

How to Retrieve Random Records through webservice method
Hi All, I'm writing an application that retrieve records from CRM 3.0 automatically using the web service. My question is, is there any way we can retrieve records randomly by using any webservice method? Like we can use RAND() or NEWID() to achieve this in SQL. Please help me if you have any idea about it. My sample method code is given below: CrmService service = new CrmService(); RetrieveMultipleRequest request2 = new RetrieveMultipleRequest(); // Create the ConditionExpression object. ConditionExpression condition = new ConditionExpression...

Random numbers
How exactly do i generate random numbers in VC++.Is the a function similar to random() in C++.Thanks in advance Fenn On Mon, 10 Jan 2005 22:42:52 -0800, "Fenn" <fenn_j@yahoo.com> wrote: >How exactly do i generate random numbers in VC++.Is the a function similar >to random() in C++.Thanks in advance > VC++i is C++. Besides, there is no random() in C++. I think you mean rand(). And besides, that is a C function imported into C++. In any event, generate random numbers in Visual C++ using MFC exactly the same way you would in any C++ (or C) program. .. yeah, b...

Random Number not so random
I am trying to create a random Job number when my form loads using the onLoad event to process this: Me.Textfield = Format(Date, "mmddyyyy") & "" & Int((99 - 1 + 1) * Rnd() + 1) Which will create a job number like 08212007** with the last two numbers being "random". The numbers are created fine, but ALWAYS the same numbers are generated on the initial load and subsequent loads of the form as follows: 0821200770 0821200753 0821200758 0821200729 0821200730 and so on. I guess it really isn't a random number function in it's truest sense. ...

Custom Contacts and Distribution Lists
Hello, If you create several cutom contacts can they be part of a distribution list? I want to be able to send a group e-mail to users who have diffrent e-mail address as a single distribution group. We are running Exchange 2003. Tahnks in advance. don't see why not...if you create mail-enabled contacts for the external people, then add them to your group, it should work... "Psycho" <Psycho@Psychotic.net> wrote in message news:OzBKc4gLEHA.892@TK2MSFTNGP09.phx.gbl... > Hello, > > If you create several cutom contacts can they be part of a distribution > lis...

Distribution List Addresses Changed
Exchange 2000 SP 3 I'm moving to a new email server, which is probably not part of my problem at all, but I figured I'd throw it out there. I have a tenant who has his own email domain that is being routed to their mailboxes through our exchange server. I have a recipient policy that is filtered just for the Mailbox store the tenants are in. That policy is below the policies for my firm. At some point during the move, all of my distribution lists changed from mycompany.com to tenantscompany.com. No other email addresses did this. Any ideas what happened? The OU's that the address...

"Distribute Campaign Activity" Unavailable
I am trying to use CRM 4.0 to setup an email campaign. As far as I can tell everything is configured correctly. However, from the campain activities window, there is no option to "distribute campaign activity". I believe it is supposed to be in the toolbar or under the "actions" dropdown, but it is not showing up in either place. Does anyone know what could be wrong? Thanks, Tony Hi Toni, are you sure you created a campaign or did you create a campaign template, if so the option isn't available in a campaign template. Benjamin "TonyLI" <talbergo@...

Seemingly random .tmp files
Seemingly random temp files are appearing in a directory where I maintain three excel files that are accessed by multiple users. I have three excel files stored in a single directory on my company's network. The files are accessed by Store Managers through our VPN via Citrix. I update these files in the mornings, and Store Mgrs add their comments throughout the day to sections relevant to their particular store. For the past three weeks, when I access the directory to update the files, there are up to 100 files that look like .tmp files. However, there are no endings on the file...

Create random file
Hi, I wish create a large random file (for testing copy in powershell between servers on WAN) i'm using a get-random function but it's very slow. A file can have a size of 1-5 Gb Do you have ideas for accelerate this program? Thanks and happy new year. My tiny program function GenChaine([int]$n) # G�n�ration d'une chaine al�atoire de n caract�res { $caracteres = "a|z|e|r|t|y|u|i|o|p|q|s|d|f|g|h|j|k|l|m|w|x|c|v|b|n" $caracteres += "A|Z|E|R|T|Y|U|I|O|P|Q|S|D|F|G|H|J|K|L|M|W|X|C|V|B|N" $caracteres += "0|1|2|3|4|5|6|7|8|9&...

Random assignments
I am attempting to set up a staff rotation. Lets say I have anywhere from 3 - 8 stations. I would like to randomly assign a name to a station on a daily basis to assure faireness. Example: Monday Bill = 1 Jane = 2 Sue = 3 John = 4 Tuesday Jane = 1 Sue = 2 John = 3 Any advise is most appreciated ...

New Tabs open Randomly
I am running XP Home (SP3) In the last few days I have had new tabs open randomly under my open tab. Can anyone tell me why this is happening and how to prevent it? Thank you. ...

Integration Manager
I have a PM Header which is pulling the data from another company database SOP10100 table. For the integration there will always only be one record. The following fields are mapped to the PM Transaction: SOPTYPE, SOPNUMBE, DOCID, DOCDATE, CUSTNMBR, CSTPONBR, SUBTOTAL, DOCAMNT, TAXAMNT This creates the Payables Transaction and I have a Text data source (csv file) that contains the GL distributions however it seems to ignore the distributions and uses the default. The relationship joins the Header Source CUSTNMBR to the Detail source "CUSTOMERID" field. I can...

Making distribution groups internal only
When you use the system manager, you are given the option to "Allow external e-mail sources..." basically saying whether someone from outside your domain can send email to the group. How can I change that attribute. I've looked in both the Exchange System Manager and the AD Users and Computers, and cannot find a setting that seems to be related to that. -garyf Gary, You should be able to do the following Open the properties of the Distribution Group and go to the Email Addresses Tab. Edit the SMTP address to be Group@company.local.dl or some other "bogus" s...