**A quick little project. **

I just happened to visit a Web site that used another Web site to generate random numbers for contest giveaways. To me, an Excel user, that seemed like a silly place to go to get a random number when you could easily generate one on the fly within Excel.

So I whipped up a tiny Excel spreadsheet to do the job. You just put the minimum value in one box and the maximum value in the other and Excel generates a random number between the two for you. To generate additional random numbers in the range, just press Command-= (on a Mac) or Control-= in Windows. The worksheet is protected so you can’t accidentally delete the formula that does the magic.

Although I created the worksheet in Excel 2004 for Mac OS, it can be opened in Excel 2003 and Excel 2007 for Windows, too.

Want a copy? Download it here.

**Update 4/22/11:** Apparently, the file has been lost in a server transfer. I’m updating it now and will restore the above link when it’s online. Sorry, folks!

**Later:** I recreated the file and put it in a new location on the server. The above link should work. Also, please note that there is now an easier way to do this with Excel; use the RANDBETWEEN function instead.

**Update 2/6/12:** This post has become very popular. But wouldn’t you rather do this the easier way? I explained RANDBETWEEN in a new post today.

Hi,

Is it me, or is the link for the xls file not working?

Thx

Nope, it’s not you. It’s me! I put in the wrong domain name. It’s fixed now — I just tested it.

Sorry about the problem, but THANKS VERY MUCH for letting me know!

Maria,

You are welcome, got the file.

FYI:

A friend wanted me to test it on Win98/Excel 2000. Works fine there too!

Thanks

maria dont spose u know how to split a four digit number like 2365 into 4 different numbers 2 3 6 5 in all different cells in excel without having to do it manually just wandering please tell me if you know a way thanks

hi jamie

say u have # 2365 in cell A1.

In cell B1 type =MID(A1,1,1) or =LEFT(A1,1)

In cell C1 type =MID(A1,2,1)

In cell D1 type =MID(A1,3,1)

In cell E1 type =MID(A1,4,1)

Hope this will help u

Regards, Rup

Good question, Jamie. It is possible. I wrote it up for you here: http://www.aneclecticmind.com/2007/07/11/excel-splitting-digits-example/

Neat and smart!

For Windows users just press F9 to recalculate/generate new number

I need to generate a random sample from a list of 1500 numbers. I knew exactly how to do that in my OLD excel, but I’m still figuring out Office 07. Where is the data analyzer tool kit?

Is it possible to generate either sequential numbers or random numbers in a cell within excel each time the document is opened?

If I’m not mistaken, Excel automatically recalculates all formulas when you open a worksheet. That would generate a new random number if you used a random number formula as discussed here.

I don’t know what you mean by sequential numbers. Do you mean the next number in a series begun in another worksheet? If so, I’m not sure how you would do that.

Maria – You are a genius! Your programme does just what I want. Thanks

Mike Reimer

Maria:

Your random number generator is super!!! Let me ask a simple (dumb) question. If I had a min range of 1 and a max range of 355 and needed to select say 10 (or “x”) numbers to audit, can a string of number be generated and listed in a cell like 80, 121, 351, 1 etc., or 10 cells populated with the selected 10?

Greg, all you need to do is use the same random number generator in multiple cells. Each cell will produce a random number. There’s a slight chance that numbers will be repeated, depending on the range and number of random numbers you create. You could get around that by generating a few extra just in case.

I got my start as an auditor, too. Fortunately, I didn’t have to generate my own random numbers. In those days, we didn’t have computers!

Great idea… I tried to copy & paste the B5 thru B8 into C5 thru C8 and could not without the password that protects the formula. Greg

I don’t recall putting a password on it. That was pretty dumb of me. I can’t figure out what the password is!

You can see the formula in the formula bar when you select Cell D8. You can use this information to recreate the worksheet as you need to.

It’s only the formula in cell B8 that you’d need to duplicate. Make sure all duplicates reference the correct min and max cells. Or simply include values instead of cell references in your worksheet.

Love the Random Generator, but I have a request. Could you instruct me how to randomly select and display the text from a cell on another sheet? Any help would be greatly appreciated!

Ellijah, this sounds like an interesting project. Give me a better idea of what would be on the other sheet. Would it be a sheet full of words or phrases in cells? Would all the content appear in one column? I’m trying to envision what that other sheet looks like so I can come up with a workable formula.

This project is for a 48 hour film festival my video production company is trying to get off the ground here in Madison, IN.

On sheet 1 there is a grid that has 4 headings: Prop, Line of Dialogue, Character, and Genre. There is also a team number (or name) running down the side. On sheet 2 is a duplicate table (minus the team number/name) that has all of the props, lines, etc. that we’ve come up with. What I’d like to do is have sheet 1 randomly select a prop from sheet 2 and repeat for the number of teams we have (say…12). I’d also like it to do this for the other 3 categories, and I’d like it to not repeat a selection that it’s made. If you’d like I could e-mail you an example I made manually. Thanks for taking an interest in this little experiment!

Sounds like an interesting use of Excel.

But it’s the “not repeat” part that makes it difficult. If you tell it not to repeat, it’s not really random anymore. That means each of the formulas would have to be different because it would have to account for each value already used and discard it.

Not sure if I can come up with the right formulas, but I’ll put my subconscious to work on it. I’ll probably figure it out the next time I’m in the shower. No promises, though.

hi i have a similar idea to the one above except perhaps a little simpler, i love this random number generator but i have a knockout draw to run and would like something that selcts from a rangeof number 1-100 but then after it makes a random selection it discards that value and the next value will only select from the remaining numbers avaliable until i get to the final number who will be the winner. for example if the first number drawn is 16 then the next number drawn would be any number 1-100 other than 16 and so on.

hope you are able to help if nt that is ok would just make my life alot easier, cheers

is there any way to change the number values to names in order to randomly generate 2 teams of 8 players out of 16 names?

Dale, you could create a lookup table with names corresponding to numbers. Then you ‘d have to use a lookup formula to spit out the names. Remember that if you simply repeated the formula for each team member, you’d have repeated names (and numbers, of course). There’s no easy way around that.

I would like to create a numer generator for documents that I create. If i say start at 10 on one line, it will take the next number from a list(lookup) when I insert new line from macro. The macro takes a copy of last line. I would also like if possible, in the number file, for the box to change colour as used and free.

Your help would be greatly appreciated.

Paul

Maria-

I think I have a repeat of a question someone else asked, but I’m not sure I saw an answer. I need to randomly sort a lengthy list of names (or more specifically, randomly select 500 names from a list of 10,000). I don’t know how to do that in excel 2007, and thought maybe a random number generator might do it, but I can’t seem to figure that out. Any thoughts? Thanks.

Hi Maria,

You are an incredible resource and I am thrilled to have found you. I was wondering if you could help me out with this problem: I am helping a grad student with some basic research. He will have 2 canals in each specimen, in which he must identify one of the canals and then assign one of 2 treatment options (either R3 or X3). The other canal would then be assigned to the other treatment option. I have already created an Excel spreadsheet with specimen number up to 40 in the first column and then Canal 1 and Canal 2 in B & C columns. It would be awesome if Canal 1 column could be randomly filled with either R3 or X3 AND then Canal 2 filled with the other option.

Is there any feasible way to do this?

You are incredibly knowledgeable and I would love to hear back from you.

Best,

rootdr

Hi This is great and almost what I was looking for which is cool. However I wondered if you could help me at all. My problem is as follows.

I have a list of approximately 2000 part numbers at my place of work in my inventory.

Our stock is terrible as to accuracy so I want something that could pick 5 random parts from a list of parts. But these parts are to be excluded from the next generation of 5 partsif you know what I mean.

Basicaly I want to do a stock check of 5 random parts a day which are not to include parts already counted?

Any ideas at all would be great (i enjoy doing this sort of thing but I am not very good at it to be honest)

Hi Maria,

This is a great resource.

I was wondering if you knew how to generate random numbers using Excel for a F distribution! I checked the data analysis pack, and it did not support F distribution. Is there a work around for this?

Thanks ,

Naveen

I am currently using the randbetween function in Excel 2007, but I don’t want a new random number every time the sheet recalcs. I only want a new number if I change one of the inputs to the formula. I am using more than random number on each tab of a multi-tab worksheet and need to control when new numbers are calcualted. How do I restrict the frrequency of calculations? Will your calcualtor allow for this?

To everyone asking me for variations on my Random Number Generator spreadsheet, I really can’t help any of you. For some of you, either what you want is impossible due to the limitations of Excel or I simply don’t know how to do it.

The solution I presented here is a very simple one designed to generate a random number between two numbers. Sorry I can’t help all of you solve your random number needs.

Good luck!

There is a slight problem with the formula provided: You need to add 1 to the difference between the minimum number and maximum number if you are going to convert the result to an integer.

In the example provided in the Excel workbook, MaxVal=10 and MinVal=1. You want to return 1 of 10 values, but MaxVal-MinVal is only 9. The result is that the two outside numbers (1 and 10 in this case) would only appear about 1/2 as many times as the numbers 2-9.

Use the following formula to return a more evenly distributed sample: =INT( RAND() * ( B6 – B5 + 1 ) + B5 )

If you insist on using the ROUND( ) function, use the following: =ROUND( RAND() * ( B6 – B5 + 1 ) + B5 – 0.5 , 0 )

Pingback: UTM Training Tool « AR Navigation

hi maria , i want generator for lotto random numbers , its play only 2 digit in every 15 min and there is some formula with 9 digits they use , its work on opposite numbers and even and odd numbers , pls help me to generate accurate no.

I need to create a random 12 digit code (to turn in to staff login bar-coded Id badges), but I need to have a record of it on my sheet without it changing. For reprinting when they inevitably misplace the badges

if I use RAND the number continuously changes I need to fix it once generated

Please help

Nikhil: You need to do a Copy and then Paste Special. The options in that dialog enable you to replace cell formulas with results.

How can I use those random numbers to refer to cells of those numbers. For example, I generated a 4 so I want my next cell to pull up the value of cell B4.

how to generate a table of 60 pulls, for a total of 800 samples, distributed on the 60 pulls

Hi,

I have a problem with the random number generator function. Every time I make any change in any field of the excel the numbers generated keep on changing. I need a solution that can fix this problem.

Can you help me out with it?

Shawnik: that’s how the Random Number generator works. It changes every time the spreadsheet recalculates. To stop the recalculation, you must copy the random numbers and use the Paste Special command to paste them as values.

You learn something new every day! Thank you for sharing! I have just generated this year’s locker combinations for our students! What an application!

i want to generate a new serial no or invoice no in contination in excel 2007?

Both Leigh Thredgold (on May 2nd, 2008) and James (on Sept 22, 2008) were looking for a similar program. As Leigh put it a spreadsheet that can provide a knockout function where once a number is generated it is not used again.

I too now need such a function. I would use the randbetween function to come up with a number, copy that number to another part of the table and then using the table range exclude all numbers previously chosen.

Maria, you were suggesting that you did not know how to do it or that it could not be done. I was wondering if Leigh or James ever created a solution to their problem?

This all sounds good. I’m looking to randomly select (2)names or even (1)name at a time from a list of 100 names in which all will have assigned numbers, but I dont want repeat selections until all names have been exhausted then have it automatically repeat the process over again. Any suggestions on what can be used. Will excel do something close to this? Thanks

it is simply super, i just need some more clarification. suppose max is 90 it should not repeat the same number and after completing 90 randum numbers it shouild stop or give a message

Krishna: Random numbers are

random. That means youcouldget duplicates.Also, this solution is not an application that generates the numbers on the fly. Instead, you can fill a worksheet with the formula to get the number of random values you need.

Awesome!

I used this technique to identify a winner in an on-line survey for my company. I had 2000 entries and this was an easy way to pick the winner.

Thanks!!

Hi Maria,

I want to create a running number for my invoice in Excel 2007. How to do?

Many thanks

I am trying to generate a grid of random numbers that is 53 x 50 and uses every number between 1 and 2650. We are doing this for a “Cow Patty Bingo” game as a fundraiser for our high school football team.

I am running into a problem using the online random number generators in that the numbers are sometimes repeated. Because the numbers represent ticket numbers, we can not have any repeats. Any ideas or suggestions?

Aaron,

I have to do the exact same thing. Can you please share your solution? Cow pie bingo ticket numbers randomly placed into a grid, no repeated numbers.

Thanks!

To Leigh Thredgold (May 2nd, 2008), James (September 22nd, 2008), and Paul (December 16th, 2009):

What you are looking for is a way to shuffle pre-selected values rather than an actual random number generator. Such a shuffling function will have to be implemented as an Excel macro rather than a single formula within a cell.

Without going into too much detail (I’ll leave that to Maria) what you will want to do is fill in a column with the pool of values from which you want to select, shuffle that column of values in-place, then select the top-most value that has not previously been selected (this can be done by placing a marker in an adjacent column to signify that the value has been used.)

Here is some sample source code written in Visual Basic (not VBA for Excel) that demonstrates the shuffling process:

Dim IntArray, Length, I as Integer

Length = CStr(Text1.Text) ' Value taken from the console

ReDim IntArray(Length)

' Fill the array with integers from 1 to Length

For I = 1 To Length

IntArray(I) = I

Next

' Swap the value of each cell in the array with some

' other random cell.

For I = 1 To Length Step 1

J = Int(Rnd * Length) + 1

Temp = IntArray(I)

IntArray(I) = IntArray(J)

IntArray(J) = Temp

Next

To use this in Excel, ignore the code near the top, you’ll only need to stuff after the “Swap the value of each cell…” comment. Replace the references to “IntArray()” with appropriate calls to “range()” and “cell()” to target the column of values you previously entered.

I’m looking to produce a list of 500 random numbers from a given non sequential list of numbers is this possible?

Hello!

Given a group of 9 sets of random numbers, using excel, how do i correctly predict the next 9.

@ICE, you cannot “predict” random numbers. They are random.

I have a rather complex SS using random number generators that then reference the line of text by =INDIRECT(ADDRESS(14+$E10,9)) Where the random number is in E10. My problem is once I generate the sheet at the beginning of the month, I want to copy it to a static spread sheet that doesn’t change the dozen or so rows and two columns that have the text determined by the random number. This way data can be input to the sheet multiple different times over the month without changing all of the text. (ie this is an randomly generated inspection schedule for the month). Any suggestions? I currently create a PDF file of the Spreadsheet, however that prevents comments in the block from expanding beyond the established space.

Is there anyone home?

@Bob: I’m not sure what you mean by “home.” I don’t

livein this blog, but I do manage it.was looking for generated numbers…

awesome, but I too ….

would like to have 4-5 different colors of EACH of 20 numbers I need

possible???

also ….sorry ..

I need random numbers to come up different every time of course…. however need every number from 1-20 to come up ONCE randomly!!….then next (20) random, would be different.

etc..etc..

Don’t want..lets say number 17 coming up twice before the 20 number cycle has gone thru

can we do that as well?

many thanx

@Bill & others…

Random numbers are RANDOM. If you don’t want a number to repeat then you don’t want a truly random number.

Hi Marie , ok forget about the colors….

and I now know random is random…

as I see you get that alot…….lol

What I thought would be a simple procedure….

…on ONE worksheet…the same

a-cell) I put down = RANDBETWEEN(1,20) ….It works fine !!

b-cell) I put the same thing down(= RANDBETWEEN(1,20) in another cell ………

when I press F9 in a-cell) …the number changes….however

the b-cell) one changes at the same time as well…….

or anything on the worksheet for that matter..

I need to have this in 6 diffrent cells but NOT changing at the same time.

I want to have to press F9 in each to get it to change.

I’ve tried all over ..is it really that difficult?

THANX again for your patience

I have a list of employee clock numbers that I would like to randomly pick 10 to 12 of those clock numbers each month. Each month I may have to delete termed employees and add new employees. Any help would be greatly appreciated! Thanks.

Everytime that you fill in a cell with anything, it generates a new random number. Is there a way to freeze the number in the cell while you fill in other cells, until you press F9 to recalculate a new number?

Microsoft changed the base seed for the random number generator. So seeding MSExcel 2004 and MSExcel 2011 give different results. Seeding should reveal the same number any time. (For those who don’t understand the root of computerized random numbers, seeding won’t make any sense, and this statement above might sound nonsense. But yielding the same results with the same seed is important.) Any hints on how to get same results from one version to another?

I am a Spanish Teacher and use this to generate a number my students have to say in Spanish.. Thanks…Now, is there a site or way I can put in 30 words and one will randomly pop up? Thanks

Thank you so much for posting, very helpful. For those who are trying to generate a random number that is not duplicated (so not a random number), a quick fix is to use the formula Maria built to whatever range you have. Then, once you have your random numbers, apply conditional formatting using the “highlight cell rules” to identify any duplicated numbers, then sort by color. Delete any duplicates.

Thanks again for publishing and explaining how to create a random number generator.

Dear Maria,

Thank you for your fantastic books.

1. Have tried to download your ‘Random Number Generator for Excel’ from the page:

http://www.mariasguides.com/2007/06/28/random-number-generator-for-excel/

Link is given as:

http://www.theflyingm.com/files/excelquickstart/random.xls

…but this goes to a ‘Page not found’ page.

What’s the correct link please?

2. Is it possible to attach the random number generator to a pre-programmed menu item in a list, so that instead of generating a number, Excel automatically produces an item randomly from a pre-made menu list?

With kind thanks,

F. Evans

F: Apparently, when moving the site to a new server about two weeks ago, I managed to delete the file. I’ll need to poke around in my archives to find a backup copy of it (if I have one)! In the meantime, maybe another reader who has downloaded it can let us know and get me a copy?

F: The file has been replaced. You can find it here: http://www.mariasguides.com/wp-content/uploads/files/random.xlsx

Years ago, I was very much interested in Excel. I managed to create Costing Sheets, Textile Formulae etc. It was the perfect tool for me.

Today, I am trying to get it to generate 6 numbers from 1 to 40 for the loto. No problem to get it to general 6 numbers between 1 and 40 in 6 cells. =randbetween(1,40) No problem to sum up the 6 random number. =sum(f1:f6) BUT, BUT, the big problem is to get it to generate a list of the sums from 95 to 155 (for example). I am looking for a formula to generate all sets of 6 numbers between 1 and 40, and them to show all the sets of 6 numbers between 95 and 155. Can Somebody help, please ?

=IF(95125,”NO”,”YES”)),”NO”)

In this example D8 is the cell with the total of the 6 random numbers in it prsumably located at D1-D6. Trying to get the NO answers to delete the D column but no luck so far.

=IF(95125,”NO”,”YES”)),”NO”)

Not sure if this will be better than what I posted before, hope so.

=IF(95lessthansymbolD8,(IF(D8greaterthansymbol125,”NO”,”YES”)),”NO”)

replace the two text strings with the proper symbol. Yes means between the numbers 95 and 125 and No means it isn’t.

Thank you, Bob.

I will try it soon.

Best regards from Mauritius.

For all those trying some variation on generating non-repeating random numbers, the easiest way does not require VBA or macros, just Rand() and Rank(). What everyone seems to be asking for is basically the software equivalent of putting the numbers from 1 to 100 in a hat and then drawing them out one by one. It will give you the numbers from 1 to 100 without repeat, but they will be in random order.

I can’t seem to figure out if I am able to upload a file showing how, so below is a description of the process. If Maria wants to post the file, she can contact me.

A1 contains “Recalculate?” B1 contains n (to start with, Y when you want new random numbers)

A2 =IF(B1=”Y”,TRUE,FALSE)

The range A5:A104 contains the numbers 1 through 100

Put the formula =IF(A5>C$4,”",IF($A$2,RAND(),B5))

into cell B5, then drag this down to row 104.

The last IF part of this formula sets up a circular reference that allows you to lock the random numbers. You will need to set your Iterations to at least 1. In Excel 2003, you get to this via ToolsOptionsCalculationIteration. If you have another version and don’t know how, search the help for “iterations” and it should tell you.

Cell C4 will contain the upper limit of the of random numbers that you need, that is, if you want non-repeating random numbers from 1 to 64, this cell should contain 64.

Put the formula =IF(B5=”",”",RANK(B5,B$5:B$104,1))

in cell C5, then drag this down to row 104.

Put the upper limit number in cell C4, put Y in B1, and you will get non-repeating, but random selection, of the numbers between 1 and your upper limit. If you want to lock these numbers down, then enter N in B1. If you want to see more random sequences, then press F9.

The upper limit in my example can’t be more than 100. However, you can expand this by dragging the cells A104, B104, and C104 down to as large as you want, up to the limits that Excel imposes. Hope this helps.

Grey

Hello Grey

what you have laidout hear is just what I’m looking for. Is it posable as you say that you could upload a file showing how to do this? or maybe you could send me the file. and lastly would you be interested in free lance some codes for me. I am trying to make a none repeating number genarater that displays nubers from a givern maximum value and keeps a record of the numbers chosen and a runing total of numbers picked like numbers called = 36. I look forward to your input.

Tote.

Grey, this is BRILLIANT. Thank you. I have been searching for about eight hours for this solution. I have implemented it, it works perfectly. My only tailoring is that it produce letters from a Scrabble tile set, so I have an extra column which does a vlookup on a two column table with numbers 1-100 on the left and the Scrabble letters on the right. The vlookup is based on the result given in your column C. I am having a problem with my vlookup, I think I need another column to represent your results in C with something like a simple number. But this is fun! Thank you!

Can anyone help me? I have a spreadsheet using RAND() and other calculations. I want the sheet to calculate the RAND() just once when it is opened and then just calculate the other formulas as required. Any ideas on how I can stop the RAND() calculating automatically after the spreadsheet is opened.

Mark,

My post of July 10, 2011 is mostly about non-repeating random numbers, but part of it is about controlling when random numbers are generated. It involves using the iterative/circular reference capability of Excel. If you set the below cells to the values that I indicate, you will see how this works in small scale.

A1 Y

A2 =If(A1=”Y”,Rand(),A2)

As I say above, you will have to set up Excel to do circular reference. Then press F9. Every time you press F9, it will give you a different random number in cell A2. Now, put N (or actually any character other than Y) in cell A1. Now press F9 as many times as you like and the random number in A2 will remain the same.

If you always ensure that cell A1 is not Y before you save it, then you will start with the same random number as you saved it with. You can then choose when to set A1 to Y and get a new random number. Hope this helps.

(In my previous posting above I used this trick when generating a series of non-repeating random numbers so I could lock them down until I wanted to generate a new set. My idea was that you could use this to to generate a random series that you could use to do something like dealing 52 cards or selecting randomly which units out of the max number of units would fail.)

I would like Excel to calculate a random number and once calculated I would like Excel to leave it alone and not recalculate it every time the spreadsheet is opened. Say I put in cell A1 that we received 145 emails, then I Excel would put a random number in cell B1 for an email to be reviewed for accuracy.

John O’Donnell,

I think my posting on Sep 24th covers this. Did you have a question about my explanation?

Grey5706

DYF8N-PRTZX-MPXGT-U9JAE-Q75CV

how can i generate a list of random numbers based on the data shown as above?

thanks and regards

Suresh

I wish to generate 30 random numbers(30days of a month) with the condition that they add up to 1.

I would be grateful if you can help me out to I generate these numbers on excel or other software.

I will apply these random numbers to simulate the rainfall in one month having known the sum of rainfall during that month.

Dear Madam,

l stumble on your website and l marvel at it.

My country have a pick 5 from 90 lotto.How can l use your random number software to create a

combination of 5 numbers from the 90 numbers with,at lease 2 numbers combined together (for

instance-1 22, 30 57, 42 90) till the 90 numbers are covered in 100-150 tickets.

Please do this for me.

Regards