Hi fellow Excel Addict,
After several weeks of not-so-spring-like weather, it looks like we are finally going to be getting some really nice weather for this long weekend...and I'm going to be away :(
I am going to Halifax to run in the 10K race at the Bluenose Marathon and, thankfully, the weather forecast there is looking even better.
In today's 'Excel in Minutes' tutorial, I'm going to show you 'How to Generate Random Numbers and Lists'. Even if you think this isn't something you would ever need to do, please read it anyway and download and follow along with my practice file. Just by playing with this, chances are that you will think of something new that you can use this for.
If you missed my 'Excel in Seconds' newsletter on Tuesday, I explained to you why 'My &%$#@ Tab Key is Scrolling Me One Screen Horizontally!" and how I fixed it. You can read all about that here.
Wishing you another great day of Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
How to Generate Random Numbers and Lists
If you need to generate a list of random numbers, Excel has a couple of functions to help you.
To generate random numbers within a range of numbers, Excel's RANDBETWEEN function makes it easy.
You simply enter the formula =RANDBETWEEN(low_number, high_number) in your cells using the lowest value and the highest value in your desired range of numbers.
There are a couple of things you'll notice about the RANDBETWEEN function:
a) Each time the sheet calculates, the random numbers change
b) The same number may be generated more than once.
How to Prevent the Numbers from Changing
To prevent the numbers from changing each time the worksheet calculates...
1) Highlight your list and move your mouse to the edge of the selected range until the mouse pointer changes to a 4-headed arrow;
2) Holding the right mouse button, drag the cells a little to the right, BUT DON'T RELEASE THE MOUSE BUTTON. Still holding down the mouse button, drag back to the original location and release the button;
3) Click the 'Copy Here as Values Only' option from the menu that pops up and the formulas will be replaced with fixed values.
Alternatively, you can highlight your list, right click Copy, right-click Paste Values.
Unique Random Numbers
If you require your list to have unique numbers, there are several possible solutions.
Here are a couple that I use because they are easy to remember...
Use RANDBETWEEN function, then Remove Duplicates
1) Select a range of cells down a column large enough to accommodate the number of values you need plus some extra to account for any duplicates that will need to be deleted. For example, we need 10 unique random numbers between 1 and 20, so we will select 14 cells;
2) Type =RANDBETWEEN(1, 20) but don't press ENTER. Instead, hold down the CTRL key and press ENTER. This will fill the formula down the cells you have selected;
3) Since the RANDBETWEEN function updates every time the worksheet recalculates, you will need to convert these formulas to fixed values. To do this, highlight the cells, right-click Copy, right-click Paste Values (or you can use the right-mouse-button-drag-copy-as-values trick explained above);
4) With the cells still highlighted, from the Data tab click Remove Duplicates and click OK. You will be left with a list of unique numbers;
5) Finally, delete any extra numbers you don't need.
Sort Unique Numbers using the RAND function
Here is another easy method for generating unique random numbers.
1) Enter the entire range of numbers from which you want to choose the random numbers in one column (e.g. 1 to 20);
2) In the next column, enter =RAND() and copy it down. The RAND function generates a value between 0 and less than 1. Note that the RAND function generates a new number every time data changes anywhere in the worksheet or if you press F9 to recalculate;
3) Next, sort the RAND column A to Z (Data , Sort, Ascending) and the order of your original list of numbers will be randomized. You will notice that after the sort operation, the RAND functions recalculated, however the data was sorted based on the original RAND values;
4) Your original numbers have now been sorted randomly.
Randomly Sort a List of Names
To randomly sort a list of names, or any other list for that matter, you can use the following simple method.
1) Select a range of cells in a column adjacent to your list;
2) Type =RAND() but don't press ENTER yet;
3) Instead, hold down the CTRL key then press ENTER. This will enter the RAND function into all of the cells you have selected;
4) Next, select a single cell in the RAND column;
5) From the Data tab click the A/Z sort command. Excel will sort your entire data table based on the values in the RAND column. You will notice that after the sort operation, the RAND functions recalculated however the data was sorted based on the original RAND values;
6) Now you can delete the RAND values.
What ways do you use random numbers and lists in Excel? Let me know.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630