Multiple Conditions for "=IF"

Ilerie

New Member
Joined
Feb 21, 2011
Messages
7
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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"})
 
Upvote 0

Forum statistics

Threads
1,224,609
Messages
6,179,874
Members
452,949
Latest member
Dupuhini

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top