Wednesday, February 29, 2012

yogi_Pick A Set Of Random IDs As Percentage Of IDs Assigned To A Set Of Names


Yogi Anand, D.Eng, P.E.                Google Spreadsheet          Post  #451          www.energyefficientbuild.com

user statq said:
I want a set of random ids picked against a subset of names, and this random sample should be percentage of the subset.
I want to get a list of random IDs corresponding to Alex, Adam, Steve. This list of random Ids should be 20% of the total number of Id under Alex.
Example : Alex has a total of 6 IDs that he has worked on, now I want a random sample of 20% of that 6 which is around 1, if he is worked on 100 IDs then I want the query to pick up a random sample of 20(20% of 100) IDs that Alex has worked on and 20% of IDs that Adam has worked on and so on.
Right now the following query works to get a list of ransom samples under say Adam alone.
=ARRAYFORMULA(SORT( A2:B7 ; RANDBETWEEN( SIGN(ROW(B2:B7)) ; 1E293 ) ; 1 ))
https://docs.google.com/spreadsheet/ccc?pli=1&key=0AlqKm4fWq06OdGpPVnVmNk1iNEpmRktaMHdCcGtlTGc#gid=0
Wanted some suggestions and help. Thanks
-------------------------------------------------------------
following is a solution to the problem