Excel Formula for Random Number Selections

[rspecianjr]

Hey everyone!

It's my first time on this forum and first of all I wanted to say thank you so much for being such a wealth of knowledge! I have learned so much from all of you!

To the problem. I am working on a lottery style workbook for my boss and I need to figure out how to generate random numbers between two numbers no including numbers that have already been picked. This workbook will be run on both a Windows machine and a Mac machine, so a Macro is out of the question.

I feel like I am really close but just can't quite figure it out. I am trying to use an array formula, here is what I have:

=INDEX(IF(ROW(A1:A59)<>G3,ROW(A1:A59)),INT(RAND()*58+1),1)

G3:K3 house the numbers for the Lottery. The above would be the formula found in H3. The only problem with the above formula is it will produce FALSE.

I really appreciate any help you can give me on this!

Many thanks,

Robert D. Specian Jr.

EDIT: Excel 2007 on Windows 7 and Excel 2008 on Mac OSX 10.6

 

[Richard Schollar]

Hi Robert

Here's one possibility:
<b>Excel 2002</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">0.147238</td><td style="text-align: right;;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">0.233052</td><td style="text-align: right;;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;">18</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">3</td><td style="text-align: right;;">0.015563</td><td style="text-align: right;;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;">14</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">0.058939</td><td style="text-align: right;;">17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;">13</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">5</td><td style="text-align: right;;">0.054628</td><td style="text-align: right;;">18</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">6</td><td style="text-align: right;;">0.715009</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">7</td><td style="text-align: right;;">0.311042</td><td style="text-align: right;;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">8</td><td style="text-align: right;;">0.951085</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">9</td><td style="text-align: right;;">0.813256</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">10</td><td style="text-align: right;;">0.201038</td><td style="text-align: right;;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;">11</td><td style="text-align: right;;">0.602668</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">12</td><td style="text-align: right;;">0.011466</td><td style="text-align: right;;">20</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">13</td><td style="text-align: right;;">0.822587</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">14</td><td style="text-align: right;;">0.843129</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">15</td><td style="text-align: right;;">0.583136</td><td style="text-align: right;;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">16</td><td style="text-align: right;;">0.185155</td><td style="text-align: right;;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">17</td><td style="text-align: right;;">0.621694</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">18</td><td style="text-align: right;;">0.872506</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">19</td><td style="text-align: right;;">0.094707</td><td style="text-align: right;;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">20</td><td style="text-align: right;;">0.333526</td><td style="text-align: right;;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=ROW(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=RAND(<font color="Blue"></font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=RANK(<font color="Blue">B1,$B$1:$B$20</font>)+COUNTIF(<font color="Blue">$B$1:$B1,$B1</font>)-1</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G1</th><td style="text-align:left">=INDEX(<font color="Blue">$A$1:$A$20,MATCH(<font color="Red">F1,$C$1:$C$20,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Formulas in B1, C1 and G1 are copied down as far as required. A new set (in col G) will be generated (between values shown in col A) at each recalculation of the sheet

 

[rspecianjr]

Thanks Richard! Works a treat.

I have changed column F to the following formula so that I can remove other numbers as well:

=ROW()+COUNTIF($A$1:$A1,"")

Simplisitic and stylish, thanks again Richard.

Regards,

Robert D. Specian Jr.