Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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. -- NR |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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 "Noelline" wrote in message ... 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. -- NR |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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 On Mon, 15 Oct 2007 13:56:01 -0700, Noelline wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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 "David Biddulph" wrote: 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 "Noelline" wrote in message ... 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. -- NR |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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 columns, B and C. In cell B2, enter the formula =RAND() and copy this formula down to match your entire data set. Then in cell C2, enter the formula =IF(B2=MAX(OFFSET($B$1,INT((ROW()-ROW($A$2))/70)*70+1,0,70,1)),"Select me","") and copy that down. Then apply a filter, and show just the "Select me" values in column C. If you need to do something with the selection, apply the filter, select the column(s) with the data that you need, use Edit / Goto.. / Special... "Visible Cells only" "OK" and then copy and paste the selection somewhere else. 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 "Kathy" wrote: 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 "David Biddulph" wrote: 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 "Noelline" wrote in message ... 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. -- NR |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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 "Kathy" wrote in message ... 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 "David Biddulph" wrote: 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 "Noelline" wrote in message ... 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. -- NR |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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 "David Biddulph" wrote: 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 "Kathy" wrote in message ... 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 "David Biddulph" wrote: 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 "Noelline" wrote in message ... 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. -- NR |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula
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 "David Biddulph" wrote: 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 "Kathy" wrote in message ... 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 "David Biddulph" wrote: 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 "Noelline" wrote in message ... 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. -- NR |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|