Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
I would like to generate some random numbers - 10 at a
time - from a list in range a1:a70 Is there an Excel or VBA Ssolution please Barry |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
I would like to generate some random numbers - 10 at a
time - from a list in range a1:a70 Is there an Excel or VBA Ssolution please Put random numbers using the RAND function in B1:B70. Then sort A and B together based only on B. Take the 1st 10 from A. Sort again for another 10. Note: Col B won't look like it's sorted because of recalculation, but it's clear that A is being shuffled. HTH, Merjet |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
Hi
"merjet" wrote in message news:Zjdhb.527730$Oz4.399041@rwcrnsc54... I would like to generate some random numbers - 10 at a time - from a list in range a1:a70 Is there an Excel or VBA Ssolution please Put random numbers using the RAND function in B1:B70. Then sort A and B together based only on B. Take the 1st 10 from A. Sort again for another 10. Note: Col B won't look like it's sorted because of recalculation, but it's clear that A is being shuffled. Too sophisticated. The formula below will do the same. =OFFSET($A$1;RANDBETWEEN(1;70);0) And the weak point is also same - when you put 10 such formulas into some range, then how to avoid the that some value is selected twice or trice? When this doesn't matter, the there you go! Arvi Laanemets |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
[This followup was posted to microsoft.public.excel.programming and a
copy was sent to the cited author.] I assume you want unique selections. In addition to the sort method suggested, check the Excel | Tutorials | 'Random Selection' page of my web site. -- Regards, Tushar Mehta MS MVP Excel 2000-2003 www.tushar-mehta.com Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article , says... I would like to generate some random numbers - 10 at a time - from a list in range a1:a70 Is there an Excel or VBA Ssolution please Barry |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
Hi
Sorry, I missed the point for sorting at first. As I found out, it works with my formula too, but you have to sort only my 10 formulas. And they can be anywhere on sheet, or even on another worksheet. Btw, I recorded a little macro for sorting formulas, and attached a shortcut to it - it's much handier this way :-)) Arvi Laanemets "Tushar Mehta" wrote in message om... The sort method doesn't yield duplicates. Since yours does, it doesn't address the OP's problem! -- Regards, Tushar Mehta MS MVP Excel 2000-2003 www.tushar-mehta.com Excel, PowerPoint, and VBA tutorials and add-ins Custom Productivity Solutions leveraging MS Office In article , says... Hi "merjet" wrote in message news:Zjdhb.527730$Oz4.399041@rwcrnsc54... I would like to generate some random numbers - 10 at a time - from a list in range a1:a70 Is there an Excel or VBA Ssolution please Put random numbers using the RAND function in B1:B70. Then sort A and B together based only on B. Take the 1st 10 from A. Sort again for another 10. Note: Col B won't look like it's sorted because of recalculation, but it's clear that A is being shuffled. Too sophisticated. The formula below will do the same. =OFFSET($A$1;RANDBETWEEN(1;70);0) And the weak point is also same - when you put 10 such formulas into some range, then how to avoid the that some value is selected twice or trice? When this doesn't matter, the there you go! Arvi Laanemets |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Numbers
Hi
There was an error - the formula must be =OFFSET($A$1,RANDBETWEEN(0,69),0) Otherwise the value in A1 is never selected, and sometimes 0 is selected (when in A71 is nothing). Arvi Laanemets "Arvi Laanemets" wrote in message ... Hi "merjet" wrote in message news:Zjdhb.527730$Oz4.399041@rwcrnsc54... I would like to generate some random numbers - 10 at a time - from a list in range a1:a70 Is there an Excel or VBA Ssolution please Put random numbers using the RAND function in B1:B70. Then sort A and B together based only on B. Take the 1st 10 from A. Sort again for another 10. Note: Col B won't look like it's sorted because of recalculation, but it's clear that A is being shuffled. Too sophisticated. The formula below will do the same. =OFFSET($A$1;RANDBETWEEN(1;70);0) And the weak point is also same - when you put 10 such formulas into some range, then how to avoid the that some value is selected twice or trice? When this doesn't matter, the there you go! Arvi Laanemets |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random Numbers excluding Previous Numbers | Excel Worksheet Functions | |||
getting numbers divisible by 4 from random numbers in ascending order | Excel Worksheet Functions | |||
Can Excel pick random numbers from 1-300 and not repeat numbers? | Excel Discussion (Misc queries) | |||
Non-random numbers generated by excel's data analysis random gener | Excel Worksheet Functions | |||
Non-random numbers generated by excel's data analysis random gener | Excel Discussion (Misc queries) |