Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Random Selection of items in Excel?
I'm creating an Excel spreadsheet that has several hundred numbers listed (in
no particular order) in the first column. The numbers are all in this format: 000.00.00.00 I was wondering if Excel has the ability to go through and select several of these numbers randomly from the column so that I can then have a random sample from my list of hundreds of numbers. Thank you- Maureen |
#2
|
|||
|
|||
Hi Maureen,
I've always found that the simplest way is to select as many adjacent cells in column B as you have numbers in column A. With those column B cells selected, enter this formula: =RAND() and hold down CTRL as you press Enter. This will enter pseudo-random numbers in column B, one for each value in column A. At this point, it usually makes sense to convert your =RAND() formulas to values. Select them, choose Edit | Copy,. then choose Edit | Paste Special, click the Values option, and click OK. (There's also a keyboard shortcut to do this, but it's not a big timesaver.) Now, suppose that your existing numbers are in cells A1:A495. If you've put random values in B1:B495, select A1:B495 and sort (either ascending or descending) on column B. This will give you a random ordering of the numbers in column A. If you want a randomly selected set of column A values, just select A1:A5. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "mnpremo" wrote in message ... I'm creating an Excel spreadsheet that has several hundred numbers listed (in no particular order) in the first column. The numbers are all in this format: 000.00.00.00 I was wondering if Excel has the ability to go through and select several of these numbers randomly from the column so that I can then have a random sample from my list of hundreds of numbers. Thank you- Maureen |
#3
|
|||
|
|||
Sorry, I should have said this:
"If you now want a randomly selected set of five column A values, just select any five contiguous values in column A." -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "mnpremo" wrote in message ... I'm creating an Excel spreadsheet that has several hundred numbers listed (in no particular order) in the first column. The numbers are all in this format: 000.00.00.00 I was wondering if Excel has the ability to go through and select several of these numbers randomly from the column so that I can then have a random sample from my list of hundreds of numbers. Thank you- Maureen |
#4
|
|||
|
|||
Thank you Conrad - I will give this a try. I'm also open to other
suggestions if anyone has any. Thanks again- Maureen "Conrad Carlberg" wrote: Hi Maureen, I've always found that the simplest way is to select as many adjacent cells in column B as you have numbers in column A. With those column B cells selected, enter this formula: =RAND() and hold down CTRL as you press Enter. This will enter pseudo-random numbers in column B, one for each value in column A. At this point, it usually makes sense to convert your =RAND() formulas to values. Select them, choose Edit | Copy,. then choose Edit | Paste Special, click the Values option, and click OK. (There's also a keyboard shortcut to do this, but it's not a big timesaver.) Now, suppose that your existing numbers are in cells A1:A495. If you've put random values in B1:B495, select A1:B495 and sort (either ascending or descending) on column B. This will give you a random ordering of the numbers in column A. If you want a randomly selected set of column A values, just select A1:A5. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "mnpremo" wrote in message ... I'm creating an Excel spreadsheet that has several hundred numbers listed (in no particular order) in the first column. The numbers are all in this format: 000.00.00.00 I was wondering if Excel has the ability to go through and select several of these numbers randomly from the column so that I can then have a random sample from my list of hundreds of numbers. Thank you- Maureen |
#5
|
|||
|
|||
Hi,
If the numbers are listed in column A (say A2:A2001), enter the following formula in B2. =INDIRECT("A"&RANDBETWEEN(2,2001)) where the two numbers in the parentheses are the first and last row numbers of your data range. Fill the formula down in column B to as many random samples as you want. The sampling would dynamically change every time Excel recalculates it (when you open the workbook or whenever you hit the F9 button). If you want a set of random samples to stay static, you have to do Copy-Paste Special-Values. If, in addition to the random samples, you also want to know their cell addresses, you could use the following formulas in B2 and C2. In B2, ="A"&RANDBETWEEN(2,2001) In C2, = INDIRECT(B2) and fill down the formulas to as many random samples as you want. Note: The RANDBETWEEN function is in the Analysis Toolpack. If it is not available in you Excel, run the Setup program to install the toolPak and then enable it by using the Add-Ins command on the Tools menu. Regards, B. R. Ramachandran "mnpremo" wrote: I'm creating an Excel spreadsheet that has several hundred numbers listed (in no particular order) in the first column. The numbers are all in this format: 000.00.00.00 I was wondering if Excel has the ability to go through and select several of these numbers randomly from the column so that I can then have a random sample from my list of hundreds of numbers. Thank you- Maureen |
#6
|
|||
|
|||
Maureen,
My Excel add-in Special Sort has a random sort option. It also provides 11 other sort options including: color, numbers only, decimal ( similar to your example), dates, and reverse. Comes with a Word.doc install/use file. Free upon request - remove XXX from my email address. Regards, Jim Cone San Francisco, USA XX "mnpremo" wrote in message ... Thank you Conrad - I will give this a try. I'm also open to other suggestions if anyone has any. Thanks again- Maureen |
#7
|
|||
|
|||
Thanks everyone - these methods work great, but is there any way to generate
all the numbers in random order all at the same time - instead of one by one? Thanks again! "B. R.Ramachandran" wrote: Hi, If the numbers are listed in column A (say A2:A2001), enter the following formula in B2. =INDIRECT("A"&RANDBETWEEN(2,2001)) where the two numbers in the parentheses are the first and last row numbers of your data range. Fill the formula down in column B to as many random samples as you want. The sampling would dynamically change every time Excel recalculates it (when you open the workbook or whenever you hit the F9 button). If you want a set of random samples to stay static, you have to do Copy-Paste Special-Values. If, in addition to the random samples, you also want to know their cell addresses, you could use the following formulas in B2 and C2. In B2, ="A"&RANDBETWEEN(2,2001) In C2, = INDIRECT(B2) and fill down the formulas to as many random samples as you want. Note: The RANDBETWEEN function is in the Analysis Toolpack. If it is not available in you Excel, run the Setup program to install the toolPak and then enable it by using the Add-Ins command on the Tools menu. Regards, B. R. Ramachandran "mnpremo" wrote: I'm creating an Excel spreadsheet that has several hundred numbers listed (in no particular order) in the first column. The numbers are all in this format: 000.00.00.00 I was wondering if Excel has the ability to go through and select several of these numbers randomly from the column so that I can then have a random sample from my list of hundreds of numbers. Thank you- Maureen |
#8
|
|||
|
|||
I just got it to work - Just what I was looking for. THANKS!!!
"Conrad Carlberg" wrote: Hi Maureen, I've always found that the simplest way is to select as many adjacent cells in column B as you have numbers in column A. With those column B cells selected, enter this formula: =RAND() and hold down CTRL as you press Enter. This will enter pseudo-random numbers in column B, one for each value in column A. At this point, it usually makes sense to convert your =RAND() formulas to values. Select them, choose Edit | Copy,. then choose Edit | Paste Special, click the Values option, and click OK. (There's also a keyboard shortcut to do this, but it's not a big timesaver.) Now, suppose that your existing numbers are in cells A1:A495. If you've put random values in B1:B495, select A1:B495 and sort (either ascending or descending) on column B. This will give you a random ordering of the numbers in column A. If you want a randomly selected set of column A values, just select A1:A5. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "mnpremo" wrote in message ... I'm creating an Excel spreadsheet that has several hundred numbers listed (in no particular order) in the first column. The numbers are all in this format: 000.00.00.00 I was wondering if Excel has the ability to go through and select several of these numbers randomly from the column so that I can then have a random sample from my list of hundreds of numbers. Thank you- Maureen |
#9
|
|||
|
|||
Hi Maureen,
Glad to hear it. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "mnpremo" wrote in message ... I just got it to work - Just what I was looking for. THANKS!!! "Conrad Carlberg" wrote: Hi Maureen, I've always found that the simplest way is to select as many adjacent cells in column B as you have numbers in column A. With those column B cells selected, enter this formula: =RAND() and hold down CTRL as you press Enter. This will enter pseudo-random numbers in column B, one for each value in column A. At this point, it usually makes sense to convert your =RAND() formulas to values. Select them, choose Edit | Copy,. then choose Edit | Paste Special, click the Values option, and click OK. (There's also a keyboard shortcut to do this, but it's not a big timesaver.) Now, suppose that your existing numbers are in cells A1:A495. If you've put random values in B1:B495, select A1:B495 and sort (either ascending or descending) on column B. This will give you a random ordering of the numbers in column A. If you want a randomly selected set of column A values, just select A1:A5. -- C^2 Conrad Carlberg Excel Sales Forecasting for Dummies, Wiley, 2005 "mnpremo" wrote in message ... I'm creating an Excel spreadsheet that has several hundred numbers listed (in no particular order) in the first column. The numbers are all in this format: 000.00.00.00 I was wondering if Excel has the ability to go through and select several of these numbers randomly from the column so that I can then have a random sample from my list of hundreds of numbers. Thank you- Maureen |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
can you filter more than two items in excel 2000 | Excel Worksheet Functions | |||
How do you create a selection box for data entry within excel | Excel Discussion (Misc queries) | |||
Auto Email from Excel doesn't appear in Sent Items | Excel Discussion (Misc queries) | |||
How can I set up the random selection of a cell from within a ran. | Excel Discussion (Misc queries) |