Assigning Probability to Randbetween Function

[mgao77]

Hi,

I have the following formula to assign probabilities to the associated figures. In this case for example, 2.65 should appear 40% of of the time. For some reason, some figures are retunrned as #N/A...Could someone help me fix this?

=CHOOSE(MATCH(RAND(),({0.1,0.4,0.3,0.1,0.05,0.05})*RANDBETWEEN(1,6)),2.5,2.65,2.7,2.8,2.6,2.3)

Thanks!

 

[Smallman]

OK so as you refresh the Random funcion the formula fails on occasion. I now see the dilema. I will have a look at it. Interesting post.

Thanks

Smallman

 

[mgao77]

Hi Smallman,

I've uploaded my file below.

Thanks for your help!

 

[Smallman]

Does this help at all. I trapped the Randbetwen at the start as well. The NAs go away.

=CHOOSE(MATCH(RANDBETWEEN(0.05,0.4),({0.1,0.4,0.3,0.1,0.05,0.05})*RANDBETWEEN(1,6)),2.5,2.65,2.7,2.8,2.6,2.3)

Take care

Smallman

 

[mgao77]

Thanks for your help Smallman.

Unfortunately, this did not do the trick as it now only returned either 2.3 or 2.5 per the parameters you set in the first "Randbetween"

 

[Hui]

The Randbetween() function only returns integers
so try something like:
=CHOOSE(MATCH(RANDBETWEEN(5,40)/100,({0.1,0.4,0.3,0.1,0.05,0.05})*RANDBETWEEN(1,6),-1),2.5,2.65,2.7,2.8,2.6,2.3)

 

[Xiq]

It maybe just my lack of knowledge, but is the hole matching a random number with a fixed array of numbers to multiply with a random number to pick a number from a fixed array of numbers... a bit of random overkill?

What about just this:

=CHOOSE(RANDBETWEEN(1,6),2.5,2.65,2.7,2.8,2.6,2.3)

 

[mgao77]

Hi Hui,

thanks for you help, but the formula still return #NA...perhaps this was more complicated than originally thought.


Hi Xiq,

That work, but I need to control the probable outcomes- For example, I need 2.8 to be the result 50% of the time.

Thanks to everyone

 

[Hui]

Actually I decided that I like:
=CHOOSE(VLOOKUP(RANDBETWEEN(0,100),{0,1;10,2;50,3;80,4;90,5;95,6},2,TRUE),2.5,2.65,2.7,2.8,2.6,2.3)
better

No.    Count    %
2.5    100    10%
2.65    431    43%
2.7    278    28%
2.8    90    9%
2.6    49    5%
2.3    52    5%
    1000    100%

 

[Hui]

@Xiq
=CHOOSE(RANDBETWEEN(1,6),2.5,2.65,2.7,2.8,2.6,2.3)
will evenly choose the numbers 2.5 etc
MGA want's to give some numbers more weighting than others

 

[mgao77]

Hi Hui,

Thanks for your help. It works quite nicely.

Can you please just explain this bit so I can learn to operate it on my own?:

(RANDBETWEEN(0,100),{0,1;10,2;50,3;80,4;90,5;95,6}


Thank you!

 

[Xiq]

Hi mgao77,

Maybe I can explain.

The {0,1;10,2;50,3;80,4;90,5;95,6} is basically a table, like this:


This is the table you ask VLOOKUP to look up the value given, returning (in this case) the value in the second column and (in this case) the range_lookup is set to TRUE.

If the range_lookup is set to TRUE, then VLOOKUP will try to find an exact or else an approximate match, picking the next largest value that is less than the lookup value. E.g.: if it looks up the value 90 to 94, it will return the value 5. Or 95 to "infinity and beyond" will return 6.... but you only pick between 0 and 100 (because of the RANDBETWEEN). So basically you now have an array with chances:

• 10% of picking the 0 to 9 --> returning 1
• 40% of picking 10 to 49 --> returning 2
• 30% of picking 50 to 79 --> returning 3
• 10% of picking 80 to 89 --> returning 4
• 5% of picking 90 to 94 --> returning 5
• 5% of picking 95 to 100 --> returning 6

 

[Hui]

Thanx Xiq

 

[Bradea Sorin Ciprian]

=CHOOSE(MATCH(RAND(),({0.1,0.4,0.3,0.1,0.05,0.05})*RANDBETWEEN(1,6)),2.5,2.65,2.7,2.8,2.6,2.3)

If i want to replace the probabilities (as those will change often) with the value of another cell does any1 have any ideea what i am supposed to do? cause simply replacing value by cell coords does not work.

For example :

=CHOOSE(MATCH(RAND(),({C5,C6,C7,C8,C9,C10})*RANDBETWEEN(1,6)),2.5,2.65,2.7,2.8,2.6,2.3) - does not work

Thanks in advance