# Excel Formula for Random Number Selections

#### rspecianjr

##### New Member
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:

Code:
``=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.

Many thanks,

Robert D. Specian Jr.

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

Last edited:

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### Richard Schollar

##### MrExcel MVP
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

##### New Member
Thanks Richard! Works a treat.

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

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

Simplisitic and stylish, thanks again Richard.

Regards,

Robert D. Specian Jr.

Replies
1
Views
162
Replies
12
Views
383
Replies
2
Views
202
Replies
4
Views
489
Replies
1
Views
528

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,477
Messages
5,831,909
Members
430,090
Latest member
bjonesh2o

### 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.

### Which adblocker are you using?

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

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