Hi Vikas, You will have to insert an extra column and put the formula =RANDBETWEEN(min_value, max_value) there. Once the formula gives you result copy the column where you had your Randbetween function copy it and select paste special > values on the same column. This will make the results of dummy column static. Now you can select as many random values in column using filter. Copy the resulting data, and paste it somewhere else. Now dummy column can be removed and origional data can be used. eg (34,45,56,67,78,89,100,54,65,76) gave (34, 45, 78, 54) when I added an extra column and used filter on dummy column to select a value of 3 and 4 in dummy column then I deleted dummy column.
You might also want to use Haseeb's solutions of putting the following functions if Data is between A and J columns: =INDEX($A$2:$J$2,RANDBETWEEN(1,COUNTA($A$2:$J$2))) =INDEX($A$2:$J$2,RANDBETWEEN(1,COUNTA($A$2:$J$2))) B3, *Array Formula must hit CTRL+SHIFT+ENTER rather than just ENTER* =INDEX($A$2:$J$2,MATCH(1,IF(ISNA(MATCH($A$2:$J $2,$A3:A3,0)),IF(RANDBETWEEN( 1,COUNTA($A$2:$J$2)),1)),0)) copy across. Hope that helps, Anand Kumar On Dec 25, 12:14 pm, Vikash Chandra <vks.chan...@gmail.com> wrote: > Thanks @ Anand, but the problem here will be it will give some random > number,which will be not from my original observation.I want the new > data set only from my original observation. For example, suppose my > original data set has (34,45,56,67,78,89,100,54,65,76) these values. > Now i want to split this data set into 70-30 Ratio who has all the > original observation only. The result should be something like > (45,100,65,76,34,89,78). All values must be in new data set taken > randomly from the original data set.Please help me out.I think there > is not any function to do this.There must be some vba code for this. > > Thanks & Regards, > Vikash Chandra > Bangalore > 9902622922 > > > > > > > > > > On Sun, Dec 25, 2011 at 6:20 AM, anandydr <anand...@gmail.com> wrote: > > Hi Vikas, > > > One way to do this would be using a function =RANDBETWEEN( min_value, > > max_value). For example if you insert a column in which you put this > > function =RANDBETWEEN(1, 100) it will generate random numbers between > > 1 and 100 then you can apply a filter on that column and select values > > below 80. It will give you about 70-75% of your data and you can use > > it then. > > This will essentially be a trial and error method and you won't get > > exact 70:30 ratio but if you could provide a sample file someone in > > the group can come up with a better solution. > > > Hope that helps, > > Anand Kumar > > > On Dec 24, 4:16 pm, Vikash Chandra <vks.chan...@gmail.com> wrote: > >> Hi Team, I am facing problem in breaking my data set into two random > >> sample,which i need for model validation.means i wanted to break my > >> entire row in two sample of say 70-30 ratio,in which the observations > >> should be taken randomly from the original observations.Is there any > >> function or macro we can write for this? Please help me out. > > >> Thanks & Regards, > >> Vikash Chandra > >> Bangalore > >> 9902622922 > > > -- > > FORUM RULES (934+ members already BANNED for violation) > > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > > will not get quick attention or may not be answered. > > > 2) Don't post a question in the thread of another member. > > > 3) Don't post questions regarding breaking or bypassing any security > > measure. > > > 4) Acknowledge the responses you receive, good or bad. > > > 5) Cross-promotion of, or links to, forums competitive to this forum in > > signatures are prohibited. > > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > > owners and members are not responsible for any loss. > > > --------------------------------------------------------------------------- > > --------------------------- > > To post to this group, send email to excel-macros@googlegroups.com > > -- -- FORUM RULES (934+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
