OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

How to pick a random cell with criteria

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Z7-852
Newbie
Newbie


Joined: 06 Nov 2011
Posts: 1

PostPosted: Sun Nov 06, 2011 10:38 am    Post subject: How to pick a random cell with criteria Reply with quote

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]
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Nov 06, 2011 3:17 pm    Post subject: Reply with quote

In a database it would be as trivial as:
Code:
SELECT TOP 1 *
FROM "Table"
WHERE <arbitrary criteria>
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
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

PostPosted: Sun Nov 06, 2011 5:04 pm    Post subject: Reply with quote

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).
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group