Multiple Conditions for "=IF"

[Ilerie]

Hi, I'm trying to simulate a game in which one randomly pulls marbles from a jar.
In my first round, the jar contains 4 black marbles and 6 white marbles.
To simulate the game's outcome I used this function:
=IF(RANDBETWEEN(0,9)<4,"Black","White")
Now, in the second round I have a jar containing 1 yellow marble, 5 red marbles and 7 green ones. How can I format "=IF" for me to have Excel display "Yellow" 1/13 times, "Red" 5/13 and "Green" 6/13 like:

=IF(RANDBETWEEN(0,12)=0,"Yellow",0<RANDBETWEEN(0,12)<6,"Red",RANDBETWEEN(0,12)<5,"Green"

This function doesn't work.

Thanks,
Ilerie

 

[Nogslaw]

Welcome to the Board!

Try this:

=LOOKUP(RANDBETWEEN(0,13), {0,1,6}, {"Yellow","Red","Green"})

Note that a function like this is volatile, so it refreshes everytime the sheet recalculates.

 

[Ilerie]

Thanks a million.

 

[Ilerie]

I still have a problem with the syntax of the function.
What should I do when I have 20 marbles out of which 9 are blue, 7 red and 4 purple?
Thanks again

 

[jasonb75]

Welcome to the Board!

Try this:

=LOOKUP(RANDBETWEEN(0,13), {0,1,6}, {"Yellow","Red","Green"})

Note that a function like this is volatile, so it refreshes everytime the sheet recalculates.

The problem with that method is lack of consistancy with the number of marbles in the jar, if you draw 3 or 4 marbles from the second jar, this could give you more than 1 yellow.

Also (0,13) gives 14 choices, not 13.

Probably a more reliable method wold be to create a list of the contents of the jar (column A) and a list of random numbers (column C) then use the sort order of the random list to make the draw (done with index and rank in column E).

Using RAND() is still volatile so will change every time the sheet is calculated, this can be stopped if needed using iterative calculation.

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Yellow</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.364464</TD><TD> </TD><TD>Green</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Green</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.065807</TD><TD> </TD><TD>Red</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Green</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.226525</TD><TD> </TD><TD>Red</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Green</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.536873</TD><TD> </TD><TD>Green</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Green</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.554253</TD><TD> </TD><TD>Green</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Green</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.726692</TD><TD> </TD><TD>Green</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Green</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.012525</TD><TD> </TD><TD>Red</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Green</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.639015</TD><TD> </TD><TD>Green</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Red</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.320179</TD><TD> </TD><TD>Red</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Red</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.877782</TD><TD> </TD><TD>Yellow</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Red</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.353256</TD><TD> </TD><TD>Red</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Red</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.52616</TD><TD> </TD><TD>Green</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Red</TD><TD> </TD><TD style="TEXT-ALIGN: right">0.551383</TD><TD> </TD><TD>Green</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C1</TD><TD>=RAND()</TD></TR><TR><TD>E1</TD><TD>=INDEX($A$1:$A$13,RANK(C1,$C$1:$C$13,0))</TD></TR><TR><TD>C2</TD><TD>=RAND()</TD></TR><TR><TD>E2</TD><TD>=INDEX($A$1:$A$13,RANK(C2,$C$1:$C$13,0))</TD></TR><TR><TD>C3</TD><TD>=RAND()</TD></TR><TR><TD>E3</TD><TD>=INDEX($A$1:$A$13,RANK(C3,$C$1:$C$13,0))</TD></TR><TR><TD>C4</TD><TD>=RAND()</TD></TR><TR><TD>E4</TD><TD>=INDEX($A$1:$A$13,RANK(C4,$C$1:$C$13,0))</TD></TR><TR><TD>C5</TD><TD>=RAND()</TD></TR><TR><TD>E5</TD><TD>=INDEX($A$1:$A$13,RANK(C5,$C$1:$C$13,0))</TD></TR><TR><TD>C6</TD><TD>=RAND()</TD></TR><TR><TD>E6</TD><TD>=INDEX($A$1:$A$13,RANK(C6,$C$1:$C$13,0))</TD></TR><TR><TD>C7</TD><TD>=RAND()</TD></TR><TR><TD>E7</TD><TD>=INDEX($A$1:$A$13,RANK(C7,$C$1:$C$13,0))</TD></TR><TR><TD>C8</TD><TD>=RAND()</TD></TR><TR><TD>E8</TD><TD>=INDEX($A$1:$A$13,RANK(C8,$C$1:$C$13,0))</TD></TR><TR><TD>C9</TD><TD>=RAND()</TD></TR><TR><TD>E9</TD><TD>=INDEX($A$1:$A$13,RANK(C9,$C$1:$C$13,0))</TD></TR><TR><TD>C10</TD><TD>=RAND()</TD></TR><TR><TD>E10</TD><TD>=INDEX($A$1:$A$13,RANK(C10,$C$1:$C$13,0))</TD></TR><TR><TD>C11</TD><TD>=RAND()</TD></TR><TR><TD>E11</TD><TD>=INDEX($A$1:$A$13,RANK(C11,$C$1:$C$13,0))</TD></TR><TR><TD>C12</TD><TD>=RAND()</TD></TR><TR><TD>E12</TD><TD>=INDEX($A$1:$A$13,RANK(C12,$C$1:$C$13,0))</TD></TR><TR><TD>C13</TD><TD>=RAND()</TD></TR><TR><TD>E13</TD><TD>=INDEX($A$1:$A$13,RANK(C13,$C$1:$C$13,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[Ilerie]

Thanks. But this is a random ergo volatile simulation.
Each time I draw a marble, I note down it's color, then put it back in the jar before pulling out an other one. The experiment is to show that one never pulls the theoretical value of marbles:

If there are 20 marbles: 4 Red, 6 Yellow & 10 Blue, after 60 draws, theoretically, I should have drawn a red marble 12 times, a yellow one 18 times, and a blue one 30 times, however the chances of that happening are minute.

 

[jasonb75]

I still have a problem with the syntax of the function.
What should I do when I have 20 marbles out of which 9 are blue, 7 red and 4 purple?
Thanks again

Based on your last reply the previously suggested method should suit what you need, for this set try

=LOOKUP(RANDBETWEEN(1,20), {9,16,20}, {"Blue","Red","Purple"})

For any others use the theoretical syntax

=LOOKUP(RANDBETWEEN(1, total in jar), {number of blue in jar, total quantity of blue and red in jar, total quantity of blue red and purple in jar}, {"Blue","Red","Purple"})

this isn't restricted to 3 colours, you can add and remove additional elements as needed.

 

[Ilerie]

Based on your last reply the previously suggested method should suit what you need, for this set try

=LOOKUP(RANDBETWEEN(1,20), {9,16,20}, {"Blue","Red","Purple"})

Thanks but each time I try it, it gives me N/A Instead of Purple.

 

[jasonb75]

Thanks but each time I try it, it gives me N/A Instead of Purple.

Oops, my mistake, I had the wrong end of the scale in the formula.

=LOOKUP(RANDBETWEEN(1,20), {1,10,17}, {"Blue","Red","Purple"})

Marbles 1-9 blue, 10-16 red, 17-20 purple.

 

[Ilerie]

The right formula is:

=LOOKUP(RANDBETWEEN(0,19), {0,9,16}, {"Blue","Red","Purple"})

Or:

=LOOKUP(RANDBETWEEN(0,Total in jar), {0,Number of blue,Number of blue + Number of Red}, {"Blue","Red","Purple"})