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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>
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
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
[email protected]XX
"mnpremo"
<[email protected]>
wrote in message
news:[email protected]...
Thank you Conrad - I will give this a try. I'm also open to other
suggestions if anyone has any.
Thanks again-
Maureen
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
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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>
Hi Maureen,
Glad to hear it.
--
C^2
Conrad Carlberg
Excel Sales Forecasting for Dummies, Wiley, 2005
"mnpremo" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks