[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Z7-852
Newbie

Joined: 06 Nov 2011
Posts: 1

 Posted: Sun Nov 06, 2011 10:38 am    Post subject: How to pick a random cell with criteria I need help to pick a random cell with criteria in open office math. I have large spread sheet with multiple cantidates and I need to pick one out of them at random. On first row I have different criteria (A column is for name and rest B to W are different attributes). After this I have a lot of rows of canditates from wicth i need to pick one out at random. This would be easy enough but I need that the canditate must fulfill certain (chainging) attributes. At the moment I have only used standart filter to found out possible canditates and used scroll to pick one out at random. But this isn't accually random pick. So I need to know how to do this propebly. So I have cells where I write what criteria should the pick have (B to W) if any. Then I need a cell that picks me one name (A) at random from all the canditates at random. Meaning that if i first need a one that has B=1 C=0 and F=1 it will pick me one that fullfills these criteria. But if my criteria chances to B=1 F=0 W=0 I can pick a new one without chaincing anything execpt the search criteria (so no new function writing). All the criteria are 0,1 type of information. [Moved to Calc – Robert Tucker, Moderator]
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10105
Location: Germany

Posted: Sun Nov 06, 2011 3:17 pm    Post subject:

In a database it would be as trivial as:
 Code: SELECT TOP 1 * FROM "Table" WHERE ORDER BY RAND()

A spreadsheet has no built-in concept of record sets.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2026
Location: Sydney, Australia

 Posted: Sun Nov 06, 2011 5:04 pm    Post subject: Perhaps something like attached doc (Random Candidate wrt 22 Criteria.ods)... http://www.mediafire.com/view/?bvpmbt1yd7golzx Start by selecting B2:W2 then pressing Backspace key to remove old selecton criteria, then use the data validity selection list (0;1) to enter new selection crieria. Selection criteria in B2:W2 left blank result in those criteria being ignored. Random name from candidates satisfying the entered selection criteria appears in X2. Helper columns Y, Z and AA can be hidden. If more than 100 names fill Y3:AA3 formulae down. Ken Johnson_________________If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum