Formula

[Guest]

I am not getting anywhere with the formulas already submitted, are ther any
other ways? I need to randomly select 50% of 11883 claims, sometimes it might
be 30% of 30,000 claims, how can I do it in a simple way, I don't really
understand formulas.

 

[David Biddulph]

If you've got your list of 11883 or 30000 claims, alongside each put the
formula =RAND(). Alongside that use the formula
=PERCENTRANK(B$1:B$11883,B2) [assuming that your RAND values are in column
B] and copy down.
Select your data range including the new columns, and choose Data/
Auto-filter. In the column with the PERCENTRANK, choose custom, Less than
or equal to: 50%, or 30%, or whatever.

 

[Gord Dibben]

And those formulas were?

Please stick to one thread. If formulas given in that thread don't do the job,
say so in that thread.

No point starting a new one.


Gord Dibben MS Excel MVP

 

[Guest]

I tried this for something I am doing and the results bring back duplicative
results. Is there a way to get a random selection showing no duplicate
results or did I do something wrong.

Here is what I am trying to do. I have a column of data (several columns)
and all I want to do is have excel randomly select 40 of my 250 items. I
don't care what column it is on or what field it is on. I want a purely
random selection. But what I was hoping for in your scenario is that excel
would number the items and then I could sort and pick the top 40 items. I
noticed there were duplicates. I also tried RANDBETWEEN and got duplicates.

Any way to get the duplication out?? I tried adding ROUND to it and that
didn't help.
--
Kathy

If you've got your list of 11883 or 30000 claims, alongside each put the
formula =RAND(). Alongside that use the formula
=PERCENTRANK(B$1:B$11883,B2) [assuming that your RAND values are in column
B] and copy down.
Select your data range including the new columns, and choose Data/
Auto-filter. In the column with the PERCENTRANK, choose custom, Less than
or equal to: 50%, or 30%, or whatever.
--
David Biddulph

I am not getting anywhere with the formulas already submitted, are ther any
other ways? I need to randomly select 50% of 11883 claims, sometimes it
might
be 30% of 30,000 claims, how can I do it in a simple way, I don't really
understand formulas.

 

[Guest]

Hi Kathy, I don't know very much on formulas so I can't answer your question
but the formula that worked for me is this one as.

You could use two columns of formulas. Let's say that you have two blank
If it doesn't help, you're best to post your question in the general field
than to respond to me.

Hope it helps...

Noelline

--
NR

I tried this for something I am doing and the results bring back duplicative
results. Is there a way to get a random selection showing no duplicate
results or did I do something wrong.

Here is what I am trying to do. I have a column of data (several columns)
and all I want to do is have excel randomly select 40 of my 250 items. I
don't care what column it is on or what field it is on. I want a purely
random selection. But what I was hoping for in your scenario is that excel
would number the items and then I could sort and pick the top 40 items. I
noticed there were duplicates. I also tried RANDBETWEEN and got duplicates.

Any way to get the duplication out?? I tried adding ROUND to it and that
didn't help.
--
Kathy

If you've got your list of 11883 or 30000 claims, alongside each put the
formula =RAND(). Alongside that use the formula
=PERCENTRANK(B$1:B$11883,B2) [assuming that your RAND values are in column
B] and copy down.
Select your data range including the new columns, and choose Data/
Auto-filter. In the column with the PERCENTRANK, choose custom, Less than
or equal to: 50%, or 30%, or whatever.
--
David Biddulph

I am not getting anywhere with the formulas already submitted, are ther any
other ways? I need to randomly select 50% of 11883 claims, sometimes it
might
be 30% of 30,000 claims, how can I do it in a simple way, I don't really
understand formulas.

 

[David Biddulph]

RANDBETWEEN returns an integer, so duplicates are likely (with a probability
depending on your defined range and the number of samples).

If you got duplicates with RAND(), then you may consider yourself very
unlucky. [I'm not seeing duplicates with 30000 samples, nor would I expect
to see duplicates, with Excel working to 15 figure precision. You'd
obviously increase the likelihood of duplicates if you used ROUND, and the
same effect would occur if you displayed with a limited number of decimal
places and used "precision as displayed".] If you merely want a ranking by
which to sort, then you can use RANK instead of PERCENTRANK, but I'd expect
that to be equally affected (or unaffected) by duplicates.
--
David Biddulph

I tried this for something I am doing and the results bring back
duplicative
results. Is there a way to get a random selection showing no duplicate
results or did I do something wrong.

Here is what I am trying to do. I have a column of data (several columns)
and all I want to do is have excel randomly select 40 of my 250 items. I
don't care what column it is on or what field it is on. I want a purely
random selection. But what I was hoping for in your scenario is that excel
would number the items and then I could sort and pick the top 40 items. I
noticed there were duplicates. I also tried RANDBETWEEN and got
duplicates.

Any way to get the duplication out?? I tried adding ROUND to it and that
didn't help.
--
Kathy

If you've got your list of 11883 or 30000 claims, alongside each put the
formula =RAND(). Alongside that use the formula
=PERCENTRANK(B$1:B$11883,B2) [assuming that your RAND values are in
column
B] and copy down.
Select your data range including the new columns, and choose Data/
Auto-filter. In the column with the PERCENTRANK, choose custom, Less
than
or equal to: 50%, or 30%, or whatever.
--
David Biddulph

I am not getting anywhere with the formulas already submitted, are ther
any
other ways? I need to randomly select 50% of 11883 claims, sometimes it
might
be 30% of 30,000 claims, how can I do it in a simple way, I don't
really
understand formulas.

 

[Guest]

Thanks David, I will look again. I tried both and thought both brought back
duplicates. I sorted afterwards and thought they both gave dupes but I will
try again using the RAND() and see what happens.
--
Kathy

RANDBETWEEN returns an integer, so duplicates are likely (with a probability
depending on your defined range and the number of samples).

If you got duplicates with RAND(), then you may consider yourself very
unlucky. [I'm not seeing duplicates with 30000 samples, nor would I expect
to see duplicates, with Excel working to 15 figure precision. You'd
obviously increase the likelihood of duplicates if you used ROUND, and the
same effect would occur if you displayed with a limited number of decimal
places and used "precision as displayed".] If you merely want a ranking by
which to sort, then you can use RANK instead of PERCENTRANK, but I'd expect
that to be equally affected (or unaffected) by duplicates.
--
David Biddulph

I tried this for something I am doing and the results bring back
duplicative
results. Is there a way to get a random selection showing no duplicate
results or did I do something wrong.

Here is what I am trying to do. I have a column of data (several columns)
and all I want to do is have excel randomly select 40 of my 250 items. I
don't care what column it is on or what field it is on. I want a purely
random selection. But what I was hoping for in your scenario is that excel
would number the items and then I could sort and pick the top 40 items. I
noticed there were duplicates. I also tried RANDBETWEEN and got
duplicates.

Any way to get the duplication out?? I tried adding ROUND to it and that
didn't help.
--
Kathy

If you've got your list of 11883 or 30000 claims, alongside each put the
formula =RAND(). Alongside that use the formula
=PERCENTRANK(B$1:B$11883,B2) [assuming that your RAND values are in
column
B] and copy down.
Select your data range including the new columns, and choose Data/
Auto-filter. In the column with the PERCENTRANK, choose custom, Less
than
or equal to: 50%, or 30%, or whatever.
--
David Biddulph

I am not getting anywhere with the formulas already submitted, are ther
any
other ways? I need to randomly select 50% of 11883 claims, sometimes it
might
be 30% of 30,000 claims, how can I do it in a simple way, I don't
really
understand formulas.

 

[Jerry W. Lewis]

It depends on the Excel version, but the algorithm for RAND that has been
used since 2003 has a period of more than 10^12.

Jerry

RANDBETWEEN returns an integer, so duplicates are likely (with a probability
depending on your defined range and the number of samples).

If you got duplicates with RAND(), then you may consider yourself very
unlucky. [I'm not seeing duplicates with 30000 samples, nor would I expect
to see duplicates, with Excel working to 15 figure precision. You'd
obviously increase the likelihood of duplicates if you used ROUND, and the
same effect would occur if you displayed with a limited number of decimal
places and used "precision as displayed".] If you merely want a ranking by
which to sort, then you can use RANK instead of PERCENTRANK, but I'd expect
that to be equally affected (or unaffected) by duplicates.
--
David Biddulph

I tried this for something I am doing and the results bring back
duplicative
results. Is there a way to get a random selection showing no duplicate
results or did I do something wrong.

Here is what I am trying to do. I have a column of data (several columns)
and all I want to do is have excel randomly select 40 of my 250 items. I
don't care what column it is on or what field it is on. I want a purely
random selection. But what I was hoping for in your scenario is that excel
would number the items and then I could sort and pick the top 40 items. I
noticed there were duplicates. I also tried RANDBETWEEN and got
duplicates.

Any way to get the duplication out?? I tried adding ROUND to it and that
didn't help.
--
Kathy

If you've got your list of 11883 or 30000 claims, alongside each put the
formula =RAND(). Alongside that use the formula
=PERCENTRANK(B$1:B$11883,B2) [assuming that your RAND values are in
column
B] and copy down.
Select your data range including the new columns, and choose Data/
Auto-filter. In the column with the PERCENTRANK, choose custom, Less
than
or equal to: 50%, or 30%, or whatever.
--
David Biddulph

I am not getting anywhere with the formulas already submitted, are ther
any
other ways? I need to randomly select 50% of 11883 claims, sometimes it
might
be 30% of 30,000 claims, how can I do it in a simple way, I don't
really
understand formulas.