Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Hi I have 2 columns as below..
Col A Col B Ref# | Name 4565 | ABC 6585 | ABC 4314 | ABC 5648 | MNO 6546 | MNO 8974 | XYZ 1346 | MNO 6584 | XYZ 1366 | ABC 4985 | XYZ 1346 | XYZ 5456 | XYZ I want to pick Ref# randomly on an average. Say, in column B there are 40 ABC, 55 MNO, 80 XYZ, 10 PQR and so on.. Then I need to pick a total on 20 ref# where I have ref# from all the Names on an average. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Hi,
what exactly do you mean by "on an average"? For example, if you say that a proportional number of items must come from each group (proportional to the total size of each group) your selection is no longer random. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Kashyap" wrote: Hi I have 2 columns as below.. Col A Col B Ref# | Name 4565 | ABC 6585 | ABC 4314 | ABC 5648 | MNO 6546 | MNO 8974 | XYZ 1346 | MNO 6584 | XYZ 1366 | ABC 4985 | XYZ 1346 | XYZ 5456 | XYZ I want to pick Ref# randomly on an average. Say, in column B there are 40 ABC, 55 MNO, 80 XYZ, 10 PQR and so on.. Then I need to pick a total on 20 ref# where I have ref# from all the Names on an average. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Hi Shane,
That is exactly what I want..proportional number of items must come from each group (proportional to the total size of each group) - Kashyap "Shane Devenshire" wrote: Hi, what exactly do you mean by "on an average"? For example, if you say that a proportional number of items must come from each group (proportional to the total size of each group) your selection is no longer random. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Kashyap" wrote: Hi I have 2 columns as below.. Col A Col B Ref# | Name 4565 | ABC 6585 | ABC 4314 | ABC 5648 | MNO 6546 | MNO 8974 | XYZ 1346 | MNO 6584 | XYZ 1366 | ABC 4985 | XYZ 1346 | XYZ 5456 | XYZ I want to pick Ref# randomly on an average. Say, in column B there are 40 ABC, 55 MNO, 80 XYZ, 10 PQR and so on.. Then I need to pick a total on 20 ref# where I have ref# from all the Names on an average. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
As posted in your multi-post in .programming
(pl don't multi-post) -------------- "Kashyap" wrote: .. proportional number of items must come from each group (proportional to the total size of each group) Here's a formulas model which delivers it .. Source data as posted assumed in A2:B13 In C2, copied down: =COUNTIF(B$2:B2,B2) Create the reference weightage table List the unique names in E2:E4 : ABC MNO XYZ In F2: =COUNTIF(B:B,E2) In G2: =F2/SUM(F$2:F$4) In H2: =SUM(G$2:G2) Copy F2:H2 down to H4 H2:H4 serves as the cumulative weightage range, ie the reference for the proportional aspect that is sought Assume we want to pick 5 ref#s In J2, copied to J6: =RAND() Then In K2, normal ENTER to confirm: =INDEX(E$2:E$4,MATCH(TRUE,INDEX(H$2:H$4J2,),0)) In L2, normal ENTER to confirm: =RANDBETWEEN(1,INDEX(F$2:F$4,MATCH(K2,E$2:E$4,0))) In M2, normal ENTER to confirm: =INDEX(A$2:A$13,MATCH(1,INDEX((B$2:B$13=K2)*(C$2:C $13=L2),),0)) Copy K2:M2 down to M6 Some explanations - what's happening: 1. Col J generates the core randomizations 2. Col K then picks up the names based on the cumulative weightage ranges that the random nums generated in col J fall within 3. Since each name is associated with a number of ref#s, col L serves to random pick from amongst the ref#s for any particular name, going by the counts of the ref#s in F2:F4 which is used as the upper limit in randbetween. Col L randomizes the relative serial numbers for the ref#s associated with that name. 4. Col M then returns the required final outputs, ie the ref#s based on the dual criteria randomized in cols K & L Press F9 to regenerate .. It is possible that repeat ref#s are generated in the output range as the number of ref#s associated with each name is quite small in the sample data (ie the Randbetween's range is small). Do a high-five if above helped in any way, press the YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Slight errata:
In K2, normal ENTER to confirm: =INDEX(E$2:E$4,MATCH(TRUE,INDEX(H$2:H$4=J2,),0)) "=" instead of "" -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Hi Max,
I'm not getting any value in column L. I'm getting the error '#NAME?' and I tried adding 2 more names in Col B and Ref# in col A, but I'm unable to get those 2 names in Col K even after editing formulas In K2, normal ENTER to confirm: =INDEX(E$2:E$4,MATCH(TRUE,INDEX(H$2:H$4=J2,),0)) Edited - =INDEX(E$2:E$6,MATCH(TRUE,INDEX(H$2:H$6=J2,),0)) and accordingly.. Pls help Thanks, Kashyap "Max" wrote: Slight errata: In K2, normal ENTER to confirm: =INDEX(E$2:E$4,MATCH(TRUE,INDEX(H$2:H$4=J2,),0)) "=" instead of "" -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
I'm not getting any value in column L. I'm getting the error '#NAME?'
Randbetween requires the Analysis Toolpak to be installed You need to install and activate it .. Check the "Analysis Toolpak" box via Tools Add-Ins Chip Pearson's page has details on the "Analysis Toolpak" at: http://www.cpearson.com/excel/ATP.htm No high-fives from you ? -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "Kashyap" wrote: Hi Max, I'm not getting any value in column L. I'm getting the error '#NAME?' and I tried adding 2 more names in Col B and Ref# in col A, but I'm unable to get those 2 names in Col K even after editing formulas In K2, normal ENTER to confirm: =INDEX(E$2:E$4,MATCH(TRUE,INDEX(H$2:H$4=J2,),0)) Edited - =INDEX(E$2:E$6,MATCH(TRUE,INDEX(H$2:H$6=J2,),0)) and accordingly.. Pls help Thanks, Kashyap |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Here's a nice working sample to demonstrate the suggested set-up:
http://freefilehosting.net/download/4575g Weighted Randomization Model.xls -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Hi Max, I'm not getting the expected result..
I need proportional number of items from each group without any duplicate.(proportional to the total size of each group) "Max" wrote: Here's a nice working sample to demonstrate the suggested set-up: http://freefilehosting.net/download/4575g Weighted Randomization Model.xls -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
.. I'm not getting the expected result ..
.. need proportional number of items from each group without any duplicate (proportional to the total size of each group) Why, that's exactly what the formulas driven model accomplishes, except for this "new" requirement: without any duplicate (I did mention this point as a caveat earlier) In the sample, one way to get a handle on this non-dups aspect is to have an eyeball alert via a formula to count the unique ref#s generated in the final o/p range M2:M6 equated to the number of unique ref#s required (5) Place this in say N1: =SUMPRODUCT((M2:M6<"")/COUNTIF(M2:M6,M2:M6&""))=5 N1 will return TRUE if there are no dups, FALSE otherwise Format N1 in red, bold, larger font, whatever to make it outstanding Just keep your eye trained on N1 as you tap F9 to regenerate Stop tapping when you see TRUE in N1 You've got the required set of unique ref#s in M2:M6 Repeat to get the next set Adapt the model set-up to suit your actuals Still no high-fives? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Hi Max,
Proportional numbers what I got in col F are as below. ABC 6 XYZ 6 MNO 4 UTV 3 But, whan I press F9 proportion differs each time as below.. 1 4 2 4 4 5 2 0 7 1 3 0 1 6 2 2 I tried this for 15 random number out of 19.. What I need is that proprotions should not change.. ABC 6 XYZ 6 MNO 4 UTV 3 Thanks.. "Max" wrote: .. I'm not getting the expected result .. .. need proportional number of items from each group without any duplicate (proportional to the total size of each group) Why, that's exactly what the formulas driven model accomplishes, except for this "new" requirement: without any duplicate (I did mention this point as a caveat earlier) In the sample, one way to get a handle on this non-dups aspect is to have an eyeball alert via a formula to count the unique ref#s generated in the final o/p range M2:M6 equated to the number of unique ref#s required (5) Place this in say N1: =SUMPRODUCT((M2:M6<"")/COUNTIF(M2:M6,M2:M6&""))=5 N1 will return TRUE if there are no dups, FALSE otherwise Format N1 in red, bold, larger font, whatever to make it outstanding Just keep your eye trained on N1 as you tap F9 to regenerate Stop tapping when you see TRUE in N1 You've got the required set of unique ref#s in M2:M6 Repeat to get the next set Adapt the model set-up to suit your actuals Still no high-fives? Click YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Believe my interp & yours on your original core issues are quite different
But, when I press F9 proportion differs each time .. Of course, isn't the choosing of the names supposed to be random? The "weightage" factor is still there, ie in the cumulative weightage range H2:H4 which provides the proportional reference/base for the name randomization process. For example, XYZ has a 42% chance of being "picked" compared to ABC's 33% and MNO's 25%. This 42% chance is actualized via its spread: 58% - 100%, ie any random num generated by RAND() [in col J] between 0.58 to 1.0 will "pick" XYZ as the random name. ABC and MNO has correspondingly lower spreads: 0 - <33% (33%), and 33% - <58% (25%), hence are proportionately less likely to be picked compared to XYZ by RAND()'s generation. And only after the name is randomly chosen, would the ref#s associated with it be randomly selected using randbetween. If you don't want the names to be randomly chosen, then just allocate the names by their ratios in F2:F5. In the sample, clear cols H, J and K. Manually enter a mix of the 3 names into K2:K6, duplicating each name to correspond roughly to their ratios in F2:F5. The rest of the set up/model execution remains unchanged. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Kashyap" wrote: Hi Max, Proportional numbers what I got in col F are as below. ABC 6 XYZ 6 MNO 4 UTV 3 But, whan I press F9 proportion differs each time as below.. 1 4 2 4 4 5 2 0 7 1 3 0 1 6 2 2 I tried this for 15 random number out of 19.. What I need is that proprotions should not change.. ABC 6 XYZ 6 MNO 4 UTV 3 Thanks.. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Ok, I have
Col A Col B Ref# Status 31850261 ABC 32430586 MNO 31946557 MNO 32118825 MNO 32455687 MNO 32455477 XYZ 32430517 XYZ 31850189 UTV 32430664 ABC 32323468 MNO 32116014 XYZ 32490083 XYZ 32504837 UTV 32505148 ABC 32505411 MNO 32505828 XYZ 32513272 XYZ 32564298 UTV 32579326 ABC Col C 1 1 2 3 4 1 2 1 2 5 3 4 2 3 6 5 6 3 4 Col E Col F Col G Col H ABC 4 0.210526316 2 XYZ 6 0.315789474 3 MNO 6 0.315789474 3 UTV 3 0.157894737 2 So, I have 19 ref# as above and Total ABC=4, XYZ=6, MON=6, UTV=3.. I want 10 random ref# (which are unique) to be picked in which ABC & UTV=2, XYZ & MNO=3 (as per col H) How do I do this? Thanks On Feb 18, 8:29*pm, Max wrote: Believe my interp & yours on your original core issues are quite different But, when I press F9 proportion differs each time .. Of course, isn't the choosing of the names supposed to be random? The "weightage" factor is still there, ie in the cumulative weightage range H2:H4 which provides the proportional reference/base for the name randomization process. For example, XYZ has a 42% chance of being "picked" compared to ABC's 33% and MNO's 25%. This 42% chance is actualized via its spread: 58% - 100%, ie any random num generated by RAND() [in col J] between 0.58 to 1.0 will "pick" XYZ as the random name. ABC and MNO has correspondingly lower spreads: 0 - <33% (33%), and 33% - <58% (25%), hence are proportionately less likely to be picked compared to XYZ by RAND()'s generation. And only after the name israndomlychosen, would theref#s associated with it berandomly selected using randbetween. If you don't want the names to berandomlychosen, then just allocate the names by their ratios in F2:F5. In the sample, clear cols H, J and K. Manually enter a mix of the 3 names into K2:K6, duplicating each name to correspond roughly to their ratios in F2:F5. The rest of the set up/model execution remains unchanged. -- Max Singaporehttp://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Kashyap" wrote: Hi Max, Proportional numbers what I got in col F are as below. ABC * * * *6 XYZ * * * * * * * *6 MNO * * * *4 UTV * * * *3 But, whan I press F9 proportion differs each time as below.. 1 4 2 4 4 5 2 0 7 1 3 0 1 6 2 2 I tried this for 15 random number out of 19.. What I need is that proprotions should not change.. ABC * * * *6 XYZ * * * *6 MNO * * * *4 UTV * * * *3 Thanks.. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Chiranth/Kashyap,
Here's the revised model, adapted to suit all of your actuals as posted below: http://freefilehosting.net/download/45919 Weighted Randomization Model v2.xls Rate this response in google & in MS' webpage -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Chiranth" wrote in message ... Ok, I have Col A Col B Ref# Status 31850261 ABC 32430586 MNO 31946557 MNO 32118825 MNO 32455687 MNO 32455477 XYZ 32430517 XYZ 31850189 UTV 32430664 ABC 32323468 MNO 32116014 XYZ 32490083 XYZ 32504837 UTV 32505148 ABC 32505411 MNO 32505828 XYZ 32513272 XYZ 32564298 UTV 32579326 ABC Col C 1 1 2 3 4 1 2 1 2 5 3 4 2 3 6 5 6 3 4 Col E Col F Col G Col H ABC 4 0.210526316 2 XYZ 6 0.315789474 3 MNO 6 0.315789474 3 UTV 3 0.157894737 2 So, I have 19 ref# as above and Total ABC=4, XYZ=6, MON=6, UTV=3.. I want 10 random ref# (which are unique) to be picked in which ABC & UTV=2, XYZ & MNO=3 (as per col H) How do I do this? Thanks |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Thnks Max, everything seems to be working good.. But, can Col K be automated
as well? I can insert ratio in Col H (manually of with help of formula). Also, may I have a macro to Tap F9 to regenerate until N1 shows TRUE.. Because all these will happen in the background. I have a different page as fromt end. Thnak you.. "Max" wrote: Chiranth/Kashyap, Here's the revised model, adapted to suit all of your actuals as posted below: http://freefilehosting.net/download/45919 Weighted Randomization Model v2.xls Rate this response in google & in MS' webpage -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Chiranth" wrote in message ... Ok, I have Col A Col B Ref# Status 31850261 ABC 32430586 MNO 31946557 MNO 32118825 MNO 32455687 MNO 32455477 XYZ 32430517 XYZ 31850189 UTV 32430664 ABC 32323468 MNO 32116014 XYZ 32490083 XYZ 32504837 UTV 32505148 ABC 32505411 MNO 32505828 XYZ 32513272 XYZ 32564298 UTV 32579326 ABC Col C 1 1 2 3 4 1 2 1 2 5 3 4 2 3 6 5 6 3 4 Col E Col F Col G Col H ABC 4 0.210526316 2 XYZ 6 0.315789474 3 MNO 6 0.315789474 3 UTV 3 0.157894737 2 So, I have 19 ref# as above and Total ABC=4, XYZ=6, MON=6, UTV=3.. I want 10 random ref# (which are unique) to be picked in which ABC & UTV=2, XYZ & MNO=3 (as per col H) How do I do this? Thanks |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
.. can Col K be automated as well?
Not sure. Fractionals vs integers issue. Try posting in .programming .. may I have a macro to Tap F9 to regenerate until N1 shows TRUE Try posting in .programming -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Kashyap" wrote in message ... Thanks Max, everything seems to be working good.. But, I can insert ratio in Col H (manually of with help of formula). Also, may I have a macro to Tap F9 to regenerate until N1 shows TRUE.. Because all these will happen in the background. I have a different page as fromt end. Thank you. |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Hi Max,
If I increase the size of COL K,L,M to 20 and A,B,C to 40, I'm not getting 'TRUE' in N1 at all.. "Max" wrote: .. can Col K be automated as well? Not sure. Fractionals vs integers issue. Try posting in .programming .. may I have a macro to Tap F9 to regenerate until N1 shows TRUE Try posting in .programming -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Kashyap" wrote in message ... Thanks Max, everything seems to be working good.. But, I can insert ratio in Col H (manually of with help of formula). Also, may I have a macro to Tap F9 to regenerate until N1 shows TRUE.. Because all these will happen in the background. I have a different page as fromt end. Thank you. |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Not sure whether you amended the eyeball alert N1's expression correctly?
viz: the output range monitored and the physical num that its equated to? Eg in my sample ver2, it was written in N1: =SUMPRODUCT((M2:M11<"")/COUNTIF(M2:M11,M2:M11&""))=10 where M2:M11 = output range, and M2:M11 is a range that spans 10 cells If the output range is now: M2:M21 N1 should be adapted to read: =SUMPRODUCT((M2:M21<"")/COUNTIF(M2:M21,M2:M21&""))=20 Btw, pl go back to ALL responses which helped you in this thread, click the YES buttons in ALL responses. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Kashyap" wrote: Hi Max, If I increase the size of COL K,L,M to 20 and A,B,C to 40, I'm not getting 'TRUE' in N1 at all.. |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Pick Ref# randomly on an average
Well, if you had adjusted the eyeball alert N1's expression correctly -- as
highlighted in my earlier response, then one other possibility to try is to simply to increase the param below in the sub (which Dave P gave to you in your .programming query) MaxCount = 1000 You could try increasing it to say: MaxCount = 5000 Or, even: MaxCount = 10000 -- Max Singapore http://savefile.com/projects/236895 Downloads:23,500 Files:370 Subscribers:66 xdemechanik --- "Kashyap" wrote in message ... If I increase the size of COL K,L,M to 20 and A,B,C to 40, I'm not getting 'TRUE' in N1 at all .. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
randomly pick a number from a set of predetermined numbers | Excel Discussion (Misc queries) | |||
Can Excel randomly pick one winner out of 100 contest entries? | Excel Discussion (Misc queries) | |||
randomly fill | Excel Worksheet Functions | |||
Array Formula to Pick Average | Excel Discussion (Misc queries) | |||
Making pick list conditional on selection from previous pick list | Excel Discussion (Misc queries) |