random number generator

Hey is there a way to generate random numbers like you would for a
draft. In other words, i have numbers 1 through 10 and generate a
random order for those?
0
pat67
5/3/2010 8:27:55 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

7 Replies
794 Views

Similar Articles

[PageSpeed] 36

Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1 and 
copy it down through B10. Recalc (F9), then sort by column B.

Hope this helps,

Hutch

"pat67" wrote:

> Hey is there a way to generate random numbers like you would for a
> draft. In other words, i have numbers 1 through 10 and generate a
> random order for those?
> .
> 
0
Utf
5/3/2010 9:23:01 PM
You would have to generate 10 random numbers then sort all by the column
the random numbers are in then delete the random number column.
 


pat67;715192 Wrote: 
> 
Hey is there a way to generate random numbers like you would for a
draft. In other words, i have numbers 1 through 10 and generate a
random order for those?


-- 
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?u=1
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=200205

http://www.thecodecage.com/forumz

0
Simon
5/3/2010 9:45:57 PM
On May 3, 5:23=A0pm, Tom Hutchins
<TomHutch...@discussions.microsoft.com> wrote:
> Use the RAND() function. If 1-10 are in A1:A10, enter =3DRAND() in cell B=
1 and
> copy it down through B10. Recalc (F9), then sort by column B.
>
> Hope this helps,
>
> Hutch
>
>
>
> "pat67" wrote:
> > Hey is there a way to generate random numbers like you would for a
> > draft. In other words, i have numbers 1 through 10 and generate a
> > random order for those?
> > .- Hide quoted text -
>
> - Show quoted text -


Rand() only generates between 0 and 1
0
pat67
5/5/2010 4:03:41 PM
Pat,

If you used RANDBETWEEN(1,10) you would be likely to get repeated numbers in 
your range.  You asked for the numbers 1 through 10 to be sorted in a random 
order, which is exactly what Hutch's solution does - RAND() is only used for 
sorting the numbers, not producing them.



"pat67" <pbuscio@comcast.net> wrote in message 
news:b0967ea1-a1de-4239-95d7-a28098067dbc@u7g2000vbq.googlegroups.com...
On May 3, 5:23 pm, Tom Hutchins
<TomHutch...@discussions.microsoft.com> wrote:
> Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1 
> and
> copy it down through B10. Recalc (F9), then sort by column B.
>
> Hope this helps,
>
> Hutch
>
>
>
> "pat67" wrote:
> > Hey is there a way to generate random numbers like you would for a
> > draft. In other words, i have numbers 1 through 10 and generate a
> > random order for those?
> > .- Hide quoted text -
>
> - Show quoted text -


Rand() only generates between 0 and 1 

0
Steve
5/5/2010 8:24:30 PM
On May 5, 4:24=A0pm, "Steve Dunn" <st...@sky.com> wrote:
> Pat,
>
> If you used RANDBETWEEN(1,10) you would be likely to get repeated numbers=
 in
> your range. =A0You asked for the numbers 1 through 10 to be sorted in a r=
andom
> order, which is exactly what Hutch's solution does - RAND() is only used =
for
> sorting the numbers, not producing them.
>
> "pat67" <pbus...@comcast.net> wrote in message
>
> news:b0967ea1-a1de-4239-95d7-a28098067dbc@u7g2000vbq.googlegroups.com...
> On May 3, 5:23 pm, Tom Hutchins
>
>
>
>
>
> <TomHutch...@discussions.microsoft.com> wrote:
> > Use the RAND() function. If 1-10 are in A1:A10, enter =3DRAND() in cell=
 B1
> > and
> > copy it down through B10. Recalc (F9), then sort by column B.
>
> > Hope this helps,
>
> > Hutch
>
> > "pat67" wrote:
> > > Hey is there a way to generate random numbers like you would for a
> > > draft. In other words, i have numbers 1 through 10 and generate a
> > > random order for those?
> > > .- Hide quoted text -
>
> > - Show quoted text -
>
> Rand() only generates between 0 and 1- Hide quoted text -
>
> - Show quoted text -

I have tried randbetween. the problem is like you said i sometimes get
the same number twice. What i was looking for is different. I wanted
to know if there was a way for me to get a random list of numbers
between 1 and 10 like this

2
4
3
1
6
10
9
7
5
8

So is there a way to do that or not? maybe with code? I don't know

Thanks
0
pat67
5/6/2010 3:56:29 PM
pat67 wrote:
> On May 5, 4:24 pm, "Steve Dunn" <st...@sky.com> wrote:
>> Pat,
>>
>> If you used RANDBETWEEN(1,10) you would be likely to get repeated numbers in
>> your range.  You asked for the numbers 1 through 10 to be sorted in a random
>> order, which is exactly what Hutch's solution does - RAND() is only used for
>> sorting the numbers, not producing them.
>>
>> "pat67" <pbus...@comcast.net> wrote in message
>>
>> news:b0967ea1-a1de-4239-95d7-a28098067dbc@u7g2000vbq.googlegroups.com...
>> On May 3, 5:23 pm, Tom Hutchins
>>
>>
>>
>>
>>
>> <TomHutch...@discussions.microsoft.com> wrote:
>>> Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1
>>> and
>>> copy it down through B10. Recalc (F9), then sort by column B.
>>> Hope this helps,
>>> Hutch
>>> "pat67" wrote:
>>>> Hey is there a way to generate random numbers like you would for a
>>>> draft. In other words, i have numbers 1 through 10 and generate a
>>>> random order for those?
>>>> .- Hide quoted text -
>>> - Show quoted text -
>> Rand() only generates between 0 and 1- Hide quoted text -
>>
>> - Show quoted text -
> 
> I have tried randbetween. the problem is like you said i sometimes get
> the same number twice. What i was looking for is different. I wanted
> to know if there was a way for me to get a random list of numbers
> between 1 and 10 like this
> 
> 2
> 4
> 3
> 1
> 6
> 10
> 9
> 7
> 5
> 8
> 
> So is there a way to do that or not? maybe with code? I don't know
> 
> Thanks


Go back and read Tom's post again.  Try it EXACTLY like he wrote it.
0
Glenn
5/6/2010 4:56:05 PM
If you're dead set against trying Hutch's solution, you could use a bit of 
circular referencing to achieve this.  First you will need to turn on 
"Enable iterative calculation" from options - read up a bit on this so that 
you understand all the implications.

Then in A1:

=RANDBETWEEN(1,10)

in A2:

=IF(($A2=0)+COUNTIF($A$1:$A1,$A2),RANDBETWEEN(1,10),$A2)

copied down A3:A10.

Hold Shift+F9 to generate a new sequence (this re-calculates the sheet).  Be 
aware that this will generate a new sequence whenever the sheet is 
re-calculated, unless you fix the number in A1.




"pat67" <pbuscio@comcast.net> wrote in message 
news:f70be74f-1910-4490-83a8-d2b84bc5f827@q30g2000yqd.googlegroups.com...
On May 5, 4:24 pm, "Steve Dunn" <st...@sky.com> wrote:
> Pat,
>
> If you used RANDBETWEEN(1,10) you would be likely to get repeated numbers 
> in
> your range. You asked for the numbers 1 through 10 to be sorted in a 
> random
> order, which is exactly what Hutch's solution does - RAND() is only used 
> for
> sorting the numbers, not producing them.
>
> "pat67" <pbus...@comcast.net> wrote in message
>
> news:b0967ea1-a1de-4239-95d7-a28098067dbc@u7g2000vbq.googlegroups.com...
> On May 3, 5:23 pm, Tom Hutchins
>
>
>
>
>
> <TomHutch...@discussions.microsoft.com> wrote:
> > Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1
> > and
> > copy it down through B10. Recalc (F9), then sort by column B.
>
> > Hope this helps,
>
> > Hutch
>
> > "pat67" wrote:
> > > Hey is there a way to generate random numbers like you would for a
> > > draft. In other words, i have numbers 1 through 10 and generate a
> > > random order for those?
> > > .- Hide quoted text -
>
> > - Show quoted text -
>
> Rand() only generates between 0 and 1- Hide quoted text -
>
> - Show quoted text -

I have tried randbetween. the problem is like you said i sometimes get
the same number twice. What i was looking for is different. I wanted
to know if there was a way for me to get a random list of numbers
between 1 and 10 like this

2
4
3
1
6
10
9
7
5
8

So is there a way to do that or not? maybe with code? I don't know

Thanks 

0
Steve
5/7/2010 7:16:26 AM
Reply:

Similar Artilces:

Importing Numbers
I imported a bunch of records that include an item number that begins with "0". Both the Excel spreadsheet cell and the Access table are marked as NUMBER. Nevertheless, when the item are imported the leading "0" was deleted. Why? How do I overcome that as I have to have the "0". -- KM Am 02.06.2010 16:44, schrieb Kevin M: > I imported a bunch of records that include an item number that begins with > "0". Both the Excel spreadsheet cell and the Access table are marked as > NUMBER. Nevertheless, when the item are imported the leadi...

Auto Account Number Generation
Here's a newbie question for which I have not yet been able to find a solution. Whenever creating a new account, I'd like the form to automatically display an account number in the accountnumber field that is one more than the max in the table. How can I automtically create this? That is how can I run a query on form load that looks up the current max account number then add one to it? ANy help would be appreciated. Only way to do this is through writing some code. For example you could achieve this using a callout. When you save a new account you can have the callout scan ...

Exchange server and huge emails generated to postmaster
I have been trying find a solution for a serious problem on exchanger server 2003 (on SBS2003). Recently probably due to a virus infection a lot emails were being generated to postmaster (1 million in 10 hours) plus badmail in badmail queue. Have checked it with norton corporate a/v but can not find any viruses. Also tried stinger but no viruses found. Have emptied all the queues. Used microsofts anti-adware program to check for adware and trojans. It came up clean. Still getting loads of emails. The only way to stop it is to stop the exchange information store service, but just dismounting...

Q: How to make "whole" numbers?
Hello NG, i want to tell excel, to make out of a number e.g. 5,93 a "whole number" (as we call it in german), wich in this case would be 5. please don't mix it up with ROUND. round would have made a 6 out of it. what i want, is that all these numbers e.g. 5,01 5,45 5,9 would be turned into a 5 thanx in advance, Tom. -- _______________________ http://tom.lautenbacher.biz _______________________ On Sun, 27 Jul 2003 02:45:25 +0200, "Tom H. Lautenbacher" <Lautenbacher@gmx.net> wrote: >Hello NG, > >i want to tell excel, to make out of a number e.g. 5,...

Parsing thru columns to find the last number
I have six columns of data and am unsure how to get to the final replacement of an item. See, col A is the itemcode, then b,c,d,e,f contain item codes that have replaced the col a item. Not all items have been replaced five times, some none, some only two, in this case col b would be blank and or col C would be blank. My goal is to find the last item in the columns that were replaced and put it in a column on it's own. So the outcome will be colA is replaced by Col G. The data looks like this. A B C D E F G z k o p i So this was replaced four times and the end res...

How do I add numbers in visible cells using Excel 97
I need to add values in visible cells using Excel 97 using a formula instead of adding values of each cell individually. Thank You John Kennedy If you mean your data is filtered, check out the SUBTOTAL function -- eg: =SUBTOTAL(9,B:B) Rgds, Andy -- Support Boris - visit http://www.ncadc.org.uk/letters/newszine36/boris.html If you are hidding the rows using a filter, then look in help at the Subtotal worksheet function. Other than that, there is no built in function that ignores hidden cells. Perhaps there is a cell value condition you can check - so you could use Sumif, use the c...

Number to Word
I want to enter "1" in cell a1 and i want "annual" to populate into b1. Iwant to enter ".52" in cell a1 and i want "semi-annual to populat into b1. so on and so on. I want excel setup up so if i type 1 that it puts annual into a cell Annual, semi annual, quarterly and monthly. Is this possible?? Thanks -- 867503 ----------------------------------------------------------------------- 8675039's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1633 View this thread: http://www.excelforum.com/showthread.php?threadid=27756 A VL...

Emailing to a fax number
what is syntax for sending an email to a fax number? That requires an email to fax service. Press CTRL+SHIFT+X in the main Outlook window to start settings things up. -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "ljc" <ljc@discussions.microsoft.com> wrote in message news:F87560BF-8996-49B3-88AF-BC49F564FC16@microsoft.com... > what is syntax for sending an email to a f...

Find cell numbers in a table so I can multiply
Hello, I have a word document and in the documents header there is a table. This table a has diferent number of cells in each row like: Row one: 2 cells Row two: 4 cells Row three: 2 cells Row four: 10 cells Row five: 2 cells What I wanted to do is multipy 3 cells together thats in row four and show the total in the same row. I have a number in the 3rd cell that needs * by the number in the fith cell that needs * by the number in the seventh cell and totaled in the 9th cell. I know I have to select the ninth cell and select table/formula then what? -- Thanks, Chad I ...

Formula to find the last number of a column (lower cell)
I have a column with numbers. I want a formula that it will be finding the lower cell that has data. For example in column A i have numbers in cells A1, A2 and A3. In cell A10 i want a fotmula. With this formula will see in cell A10 the number that i have in cell A3. If i put a number in cell A4 i want that number to be shown in cell A10. Try the below in cell A10 =LOOKUP(10^10,A1:A9) -- Jacob (MVP - Excel) "yiannis5231" wrote: > I have a column with numbers. I want a formula that it will be finding the > lower cell that has data. For example in colu...

Efficiently Generate 88 New Chart Sheets
Gang, I've got a simple X*Y chart with year (1977-present) on the x-axis and an estimate of deer population size on the y-axis (actually, there may ultimately be 2 estimates/series). There is nothing really all that fancy about it. It will be updated each year with the current year's estimate. The problem is, I need 87 more just like it - 1 for each of the 88 units that I manage. I have been reading VBA and Excel books and I have yet to find an example on how to "mass produce" a bunch of chart sheets. I take that back - a simple "FOR NEXT" loop will generate t...

Mapisp32.exe has generated errors and will be shut down
One of my coworkers is getting this error whenever Outlook97 checks for email. She is running Office97 Pro in WIndows 2000 on a 600mhz PIII with 128MB RAM. >-----Original Message----- >One of my coworkers is getting this error whenever >Outlook97 checks for email. She is running Office97 Pro in >WIndows 2000 on a 600mhz PIII with 128MB RAM. >. > I almost forgot to mention that I tried the fix listed in Microsoft Article 296412, with no success. Here is the URL to that fix. http://support.microsoft.com/default.aspx?scid=kb;en- us;296412 ...

Changing initials into numbers
I have data that I download into Excel and initials are used for different people. I need to give those initials a number in excel for example MJ = 01, HS = 02..etc. Thank you. On 2/3/2010 9:26 AM, Kathie wrote: > I have data that I download into Excel and initials are used for different > people. I need to give those initials a number in excel for example MJ = 01, > HS = 02..etc. Thank you. see VLOOKUP in Excel help. Bill Kathie You have to be more specific. Where are the initials (column, row, sheet)? Where do you want the numbers placed? HTH Otto &quo...

the number "3" displays like an exposant
je ne comprend pas pourquoi le chiffre 3 apparait comme un exposant dans mon classeur ...

Socket relared problem (getting free port number dynamically) #2
Hi all, i have one problem related to thread. i am having my main thread running which will on some event create a thread for validation purpose and then will start working. and now this validator thread will create a socket and it will bind to port 20111. and then will wait for client to connect. but before waiting for client to connect it will send a message to the main thread that validation is done. but in this way i need to stick with port 20111.which i really don't want. Is it possible for me to get a free port from operating system or from winsock library and then i should bind t...

Number of users
Can anybody tell me the number of users that can use Excel at any one point. I am fairly sure that a number of users can use it at once, but will they have to look at the spreadsheet in a 'read only' state with only one user having the full functions of the spreadsheet. So basically, is it true that a number of users can use it, but the spreadsheet is in a read only format? any help will be most appreciated. Mark -- pompey_student ------------------------------------------------------------------------ pompey_student's Profile: http://www.excelforum.com/member.php?action=ge...

phone numbers #2
how do i add a new phone number field to the drop down list in outlook? You mean besides the 17 or so that are there? You can't add a new type of phone number. Sorry. -- Patricia Cardoza Outlook MVP Author - Special Edition Using Microsoft Office Outlook 2003 Lead Author - Access 2003 VBA Programmer's Reference Author - Absolute Beginner's Guide to Microsoft OneNote 2003 http://blogs.officezealot.com/cardoza "shauna" <anonymous@discussions.microsoft.com> wrote in message news:a23801c486cc$833670d0$a601280a@phx.gbl... > how do i add a new phone number field ...

Formatting cells for big numbers and formulas ...without the formu
Hello, I am stuck with this: If I want cells to display like 20-digit numbers in numerical form (that is, not the xxx+EXX way), formatting them as a text is okay. But then I need to add formulas and want the results to be displayed. But as the cells are text formatted, the result in the cell is of course the text of the formula plus the number. So my question is - in a cell I want e.g. "12345678901234567890-sum(A1,B1)" and I want the cell to display the resulting number, not the formula itself. How do I achieve this? The maximum precision for a number is 15 digits according to...

entering numbers that begin with zero
I often enter manufacturer model#'s into my spreadsheet. If they begin with zero Excel automatically the zero. What can I do to change this? On Sep 10, 1:15=A0pm, Shannon <Shan...@discussions.microsoft.com> wrote: > I often enter manufacturer model#'s into my spreadsheet. =A0If they begin= with > zero Excel automatically the zero. =A0What can I do to change this? You can format the cells to accept leading zero's. From the format cells window, look for custom number formats, then in the Type field type 0000 , with each zero representing the number of digits you w...

numbers not correct
When I try to add numbers to a cell, they are converted to a date. How can I correct this? I have the "general" numbers tab selected. (Excel 2007) Think you could either precede the entry with an apostrophe ('), or pre-format the data entry col as text. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Chicken Dogs" wrote: > When I try to add numbers to a cell, they are converted to a date. How can I > correct this? I have the "general" numbers tab selected. (Excel 2007) ...

Add a number based on list of criteria ??
Example: Cell A = 50, Cell B = 96%, Cell C = Cell A + (Cell A x Cell B) = 98 I want to be able to compare the list of criteria and add a number to Cell C accordingly For example: If Cell B is between 95 and 100%, Add 1 to the total in Cell C If between 85 and 94%, add 2... etc. There are more than 7 so I know I can't simply nest If statements... Any advice on how I could fairly simply accomplish this? TIA! One way: Put your % bands in a table (say in J1:Kx, e.g.: J K 1 0 11 2 5% 10 3 15% 9 .... 10 85% 2 11 ...

Repeat values in column A a certain number of times depending on the value in column B
Imagine a set of data as set out below: Column A Column B Apples 24 Pears 36 Oranges 8 I want to poplulate a column (for exampel column A on a new sheet), where the values in column A will be repeated as many times as the value in column B Thus the first 24 rows will say Apples, the next 36 rows will Pears, Oranges. I need a formula that recogonizes that when it gets to row 25 that it should no longer need to copy Apples, but then switch to pears. This might sound like an unusual request but if I can grasp a way to do this I can create the table I need and populate the rest...

Newbie Help for Custom Number Formats
Hey All, I am working on a spreadsheet that I would like to have it append the inch symbol as part of the number format ("). I figured out that I can add custom suffixes with the use of the quotation marks, but I can't place a quotation mark inside of quotes and get what I need. Is there any way to have Excel do this for me? I know it seems like a small thing to do, but having Excel add this for me would save me a lot of time with formulas and all. Example: 24" Thanks in advance. JB -- joshb416 ------------------------------------------------------------------------ j...

AUTO NUMBERING #2
hi all,i have made an invoice template for my work.how can i get these invoices to sequentially number each time i use the invoice and save as iun the customers name?any suggestions will be greatly appreciated. thanks wyn. -- WYN Hi one way (using the Windows registry for storing the last number). Put the following code in the workbook module (not in a standard module) of your template: - It changes cell A1 of the first sheet - you may change the key identifiert according to your needs (e.g. DEFAULTSTART, MYLOCATION, etc.) Private Sub Workbook_Open() Const DEFAULTSTART As Int...

HELP: How To Turn Off Generation of Secondary SMTP Addresses
Hello, I've got an exchange setup that receives mail on domain-a.com (primary SMTP) and domain-b.com, domain-c.com etc... This works fine, apart from, for every new user/mailbox, it assigned the user the primary address and all the secondary addresses. I'd like to have it so that only the primary address is created, and the secondary address are not. As only a handful of users need/should have secondary SMTP addresses, so it's easier to add them manually as needed. However, i can't see how i turn off generation of the secondary addresses, without breaking the reciving...