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

Random Choices

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


Joined: 06 Sep 2008
Posts: 3

PostPosted: Sat Sep 06, 2008 6:19 am    Post subject: Random Choices Reply with quote

Greetings!

I need a function or formulae that would enable the Calc to bring me a series of random choices from a table, but the choices, while random, should take inconsideration the probability of any of the itens... It would work this way:

Item Probability
A 2
B 5
C 3

I need a way to choose randomly in this table so the item B would be choosen 50% of the time, item C 30% and item A 20% of the time....

Can anyone help me? I´m stuck....

Thanks

Edit: the table that i need to work with have nearly 200 itens...
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Sat Sep 06, 2008 6:46 am    Post subject: Re: Random Choices Reply with quote

Makinus wrote:
Greetings!

I need a function or formulae that would enable the Calc to bring me a series of random choices from a table, but the choices, while random, should take inconsideration the probability of any of the itens... It would work this way:

Item Probability
A 2
B 5
C 3

I need a way to choose randomly in this table so the item B would be choosen 50% of the time, item C 30% and item A 20% of the time....

Can anyone help me? I´m stuck....

Thanks

Edit: the table that i need to work with have nearly 200 itens...


I can do the randon choices but I don't see how to get the value.

For the random choices , simply expand the items by weight in a column A. Run a series of numbers 1 to length of Column A entries) in column B. Then use the RANDBETWEEN()
function to pick out values in B. This maps into A and you have your random selection.

How to use the results of RANDBETWEEN to get the value from column A I have not figured out.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Makinus
Newbie
Newbie


Joined: 06 Sep 2008
Posts: 3

PostPosted: Sat Sep 06, 2008 7:25 am    Post subject: Reply with quote

Yeah, i tought about doing it in the way that you describe... and getting the colun A values would not be a problem... the problem is that the probability of the itens aren´t all integers like in the example i put here, some have fractional values, so it would take a very huge table to account for all the possible fractionals....

Thanks anyway... i´ll continue searching for the answer...
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Sat Sep 06, 2008 8:26 am    Post subject: Reply with quote

Makinus wrote:
Yeah, i tought about doing it in the way that you describe... and getting the colun A values would not be a problem... the problem is that the probability of the itens aren´t all integers like in the example i put here, some have fractional values, so it would take a very huge table to account for all the possible fractionals....

Thanks anyway... i´ll continue searching for the answer...



This is herasy but I'd expand the vector ( table ) as suggested above and use the sample function in R for Statistics

It's a trivial exercise there.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sat Sep 06, 2008 11:41 am    Post subject: Reply with quote

A1:A10
A
A
B
B
B
B
B
C
C
C
B1:B10 =RAND()
C1 =RANK($B1;$B$1:$B$10)
D1 =INDEX($A$1:$A$10;$C$1)

Hit F9
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Sep 07, 2008 1:18 am    Post subject: Reply with quote

Assuming letters A to G in A1 to A7, probabilities of each in B1 to B7, then put:

0 in C1

=C1+B1 in C2

Copy C2 and paste it in C2 to C8 (one row further down than the other stuff) to give a cumulative "probability" total.

Put:

=rand()*C8 in D1

=LOOKUP(D1;C1:C7;A1:A7) in E1

Press F9 to get a new random letter based on the weighted relative probabilities.

NOT checked very thoroughly...
Back to top
View user's profile Send private message
Wandering
OOo Enthusiast
OOo Enthusiast


Joined: 18 Oct 2007
Posts: 190

PostPosted: Sun Sep 07, 2008 12:04 pm    Post subject: Reply with quote

form a random number from 0 -1, test it. If it is < 0.3, return the letter with .3 probabality
If it is > .3 but less than .8 return the letter with .5 probability. If it is none of these return the third letter
Back to top
View user's profile Send private message
Makinus
Newbie
Newbie


Joined: 06 Sep 2008
Posts: 3

PostPosted: Mon Sep 08, 2008 2:42 am    Post subject: Reply with quote

Thanks, the lookup function did it! i only needed to change some things because my data source had some peculiarities (varying probabilities depending of the hipotesis), but now it´s working exactly like i needed!

Thanks....
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