Not FAIR Dice

vazi01 · Feb 17, 2010

hello all
plz hlp:
I have a dice /coin problem.
the general problem is like this how to make a not fair dice/coin?
in particular I have , let say a dice , with 22 faces .
I want to get more often some of the faces then the others, even more I want that some faces to be obtained with some particular incidence. In example lets say the face F11 should appear 20 times if I roll the dice 100 times(20%) so if I throw the dice 400 times ... I should get similar results (20% +/- a percent) ... and so one for all the faces.
the input data looks like this:
Face % appears in 100
1 1.5%
2 16%
3 ...
4 etc
5
6
7
.
.
.
n
.
.
19
20
21 10%
Neg 6%
total= 22 faces 100%

The output data should be a in a cell, so if I fill down a series - I ll get the expected values.
I have tried these: =INDEX($A$2:$A$23,RANDBETWEEN(1,COUNTA($A$2:$A$23)),1)
.. but do not know how to "manipulate" the output, to take in the second column...
thx u all for u time,
Vali.
__________________

vazi01 · Feb 17, 2010

to be more specific I ll try to add some details about the expected output.
so the output should look this

Code:

 roll  No ........... face ID
1                              F11
2                              F12
3                              F11
4                              F22
5                              F5
6                              F21
7                              F10
8                              F14
9                              F11
10                            F4
.                              .
.                              .
.                              .
.                              .
.                              .
400                           F2
_____________________________

so if I do a a check like "=count if (C:C400,"F5") /400" I ll get a % very close to that I had inputed as incidence initially..
hlp

Oaktree · Feb 17, 2010

Sounds like a job for the random number generator (part of the analysis toolpak, found under tools --> data analysis --> random number generator) with a discrete distribution.

alphadown · Feb 17, 2010

you could generate a vlookup table with cumulative % chances as the lookup value
eg
1.5% F1
17.5% (1.5% + 16%) F2
...

100% F22

=rand() will generate numbers between 0-1 (= 0%-100%), so Faces with larger ranges are chosen more often
e.g. F1 will be chosen if the random number is between 0 and 0.015, while F2 will be chosen if it is above 0.015 up to 0.175. etc.

vazi01 · Feb 18, 2010

PLZ hlp improve constrictions... the results are far beyond expected vallues

I have tryed this input:

Code:

Column1(A)	Face ID(B)	prob (C)??	cumulative(D)??
1	f1	1.429%	0
2	f2	2.857%	4.29%  =(sumC$2:C3)
3	f3	1.429%	5.71%
4	f4	5.714%	11.43%
5	f5	7.143%	18.57%
6	f6	8.571%	27.14%
7	f7	10.000%	37.14%
8	f8	5.714%	42.86%
9	f9	4.286%	47.14%
10	f10	2.857%	50.00%
11	f11	2.857%	52.86%
12	f12	4.286%	57.14%
13	f13	5.714%	62.86%
14	f14	2.857%	65.71%
15	f15	5.714%	71.43%
16	f16	4.286%	75.71%
17	f17	4.286%	80.00%
18	f18	2.857%	82.86%
19	f19	8.571%	91.43%
20	f20	5.714%	97.14%
21	f21	1.429%	98.57%
22	f22	1.429%	100.00%

and on the output :

Code:

roll result Method 1 (E)
=VLOOKUP(MATCH(RAND(),D$2:D$23),$A$2:$B$23,2,) and filled down 399 rows

to check the results:
I assumed tollerance shoould be max 1/2 of the minimum incidence (cell j2). I filled 2 collumns (k & L). column K is identical with column A.

Code:

column K       roll result Method 1(L)
f1               [SIZE="3"]=IF(COUNTIF(F$2:F$400,$B2)/COUNTA(F$2:F$400)=0,"not acceptb incidence=0", IF(ABS($C2-COUNTIF(F$2:F$400,$B2)/COUNTA(F$2:F$400))>$J$2,"not accept perf",COUNTIF(F$2:F$400,$B2)/COUNTA(F$2:F$400)))[/SIZE]
f2                       fill down
f3
f4
f5
f6
f7
f8
f9
f10
f11
f12
f13
f14
f15
f16
f17
f18
f19
f20
f21
f22

but it doesn't seems to work...

vazi01 · Feb 18, 2010

Oaktree said:
Sounds like a job for the random number generator (part of the analysis toolpak, found under tools --> data analysis --> random number generator) with a discrete distribution.

I tried these too... but it doesn't fit my tolerance bellow 1%(=half of the min probability)

JackBean · Feb 18, 2010

vazi01:

For another variation, you should be able to adapt the solution in this post:

Validating random output in Excel2007 without VBA
http://www.mrexcel.com/forum/showth...e+random+number+generator+Normal+distribution

The larger the number of throws, the closer the distribution will become to "expected", but you will never get expected exactly.

I am not sure if this is what you mean by tolerance.

xenou · Feb 18, 2010

I'm thinking out loud -- and just realized I didn't check out the last link -- but I came up with this way to organize the problem. Completely untested:

PSEUDOCODE:

Code:

Function Foo()
[COLOR="SeaGreen"]
'//Construct an array with the desired proportional "unfair" outcome for each side
     '//i.e., if 3 should appear 4 times out of 100, it is here 4 times, and so on[/COLOR]
a=Array(1,1,2,2,3,3,3,3,4,5,5,6,7,7,7 ... 22,22,22,22,22,22,22,22,22,22,22,22)
[COLOR="SeaGreen"]
'//Now pull an index value from 0 to 99 randomly[/COLOR]
x = Round(Rnd(),0)

[COLOR="SeaGreen"]'//And return the element at that index[/COLOR]
x = a(x)

[COLOR="SeaGreen"]'//We hope that this will return a proportional greater number of elements
    '//to the extent that they are in the original array at their "theoretical" distribution[/COLOR]

End Function

I believe the Round() function in VBA uses bank rounding and so should fall on both sides of .5 equally over time - but I didn't really clarify how to generate the random number from 0 to 99 anyway. A similar construction could be done with a range of values on a worksheet - but there's problems with rounding and real randomness possibly.

Edit: And now, seeing this laid out this way, ... I wonder if you couldn't just get the same result by "multiplication", once you've determined the random result as an integer (Face 1 - multiply by x, Face 2, multiply by y ... Face 22, multiply by 20)... But you'd need more than 100 trial I guess, and as above, your integers better not be skewed to start with....

Blade Hunter · Feb 18, 2010

You might need a Randomize command at the start of your code. Dunno if it is still valid but back when I learned to program we were told that it would pick the same series of number each time if you didn't do that.

Not FAIR Dice

vazi01

New Member

Excel Facts

vazi01

New Member

Oaktree

MrExcel MVP

alphadown

New Member

vazi01

New Member

vazi01

New Member

JackBean

Active Member

xenou

MrExcel MVP

Blade Hunter

Well-known Member

Similar threads

Forum statistics

Share this page

Not FAIR Dice

New Member

Excel Facts

New Member

MrExcel MVP

New Member

New Member

New Member

Active Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock