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.
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 )
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 you could get 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?
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:
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.
[...] thanks to Maria Langer for designing the foundation for this [...]