View previous topic :: View next topic 
Author 
Message 
Makinus Newbie
Joined: 06 Sep 2008 Posts: 3

Posted: Sat Sep 06, 2008 6:19 am Post subject: Random Choices 


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 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

Posted: Sat Sep 06, 2008 6:46 am Post subject: Re: Random Choices 


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 


Makinus Newbie
Joined: 06 Sep 2008 Posts: 3

Posted: Sat Sep 06, 2008 7:25 am Post subject: 


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 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

Posted: Sat Sep 06, 2008 8:26 am Post subject: 


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 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Sat Sep 06, 2008 11:41 am Post subject: 


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 


RickRandom Super User
Joined: 27 Jan 2006 Posts: 1082 Location: UK

Posted: Sun Sep 07, 2008 1:18 am Post subject: 


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 


Wandering OOo Enthusiast
Joined: 18 Oct 2007 Posts: 190

Posted: Sun Sep 07, 2008 12:04 pm Post subject: 


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 


Makinus Newbie
Joined: 06 Sep 2008 Posts: 3

Posted: Mon Sep 08, 2008 2:42 am Post subject: 


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 


