Excel’s RAND and RANDBETWEEN functions aren’t very well known, but they can be quite handy.
RAND Function
RAND is a simple function that returns a random number between 0 and 1.
It is entered
=RAND()
That’s it…simple.
RANDBETWEEN Function
RANDBETWEEN is slightly more complicated.
=RANDBETWEEN(bottom,top)
Where ‘bottom’ is the lowest number you want returned and ‘top’ is the highest.
For example, to return a number from 1 to 100 you would enter:
=RANDBETWEEN(1,100)
If you prefer whole numbers then RANDBETWEEN is the best option.
Pressing F9 on your keyboard will calculate new random numbers, as will entering a new formula anywhere else in the workbook or any formatting changes like inserting columns/rows etc.
If you don’t want it to update just turn off automatic calculations:
Go to the Windows Button > Excel Options > Formulas section – see image below
Note: if you get an error when you use the RAND function in Excel you will need to install the Analysis ToolPack add-in first.
USES for RANDBETWEEN
RANDBETWEEN on its own has some limitations, for example you could end up with duplicate random numbers, which is no good if you want to randomly number a list.
RANDBETWEEN and CHOOSE Example
Let’s say we have to select someone to work each Saturday…you’re not going to be the most liked boss, but if you can blame it on Excel’s Random Selection it might reduce some of the flack!
Below is a list of 7 employees and we have to choose one to work each Saturday.
Our formula is:
Use RAND with Microsoft Word!
Microsoft Word also had a RAND function which is handy when you’re setting up templates and you want to insert some text to experiment with layouts.
You enter it the same way you enter it in Excel. That is, simply type =RAND() into your document and press enter. Voila, you will be presented with 3 paragraphs of text.
If you want more or less than 3 paragraphs simply enter the number of paragraphs inside the brackets like this:
=RAND(5) This will give you 5 paragraphs of text.
How to Calculate Interest on Savings in Excel
David N
I know this is an old post, but it can still come up as an internet search result; so I wanted to comment. As others have noted, the combination of RANDBETWEEN and CHOOSE is not a valid way to guarantee a lack of repetition. This makes sense because the very definition of random is that repetition (even in consecutive calls) is possible. So each time the example function is executed, it simply gets a random number between 1 and 7 and then “chooses” that person.
Those commenters and others who would like a range-based approach should use INDEX instead, recognizing that this too allows for repetition.
=INDEX(D4:D10,RANDBETWEEN(1,7))
Achieving random numbering or sorting without repetition is more complicated with one numbering method for the given example being as follows.
Step 1: Enter this array formula in cell E4 and execute with Ctrl+Shift+Enter
{=SMALL(IF(COUNTIF(E$3:E3,ROW(INDIRECT(“1:7”)))=0,ROW(INDIRECT(“1:7″)),””),INT(RAND()*(7-ROWS(E$4:E4)+1)+1))}
Step 2: Copy the formula to the remaining cells (down to E10)
If needed, the formula could be made more dynamic by using COUNTA with INDEX or OFFSET (maybe even with one or more Defined Names) and replacing the two hard-coded instances of INDIRECT(“1:7”) and the one instance of a 7.
Mynda Treacy
Nice tips. Thanks for sharing, David.
Leon
Hi,
Greeting from Malaysia.
Currently I’m study to do a simple random name picker which need to done in excel.
Below is my conditions:
1. sample size, n=500;
2. The previous selected name (a.k.a. cells) need to be omitted so that the name will not be repeated again.
I had done it by using =INDIRECT(CONCATENATE(“A”, RANDBETWEEN(1,500)))
[I put the name list in cell A],
I’m also tried to use the CHOOSE function that you wrote in this blog. Well, it can be randomly selected name. However, some name will still be repeated again because I had no wrote any function to fulfill the condition 2.
Can you help me in this instance?
Thanks!
Catalin Bombea
Hi Leon,
You have to prepare a sample file, there are a few thinks that can be clarified by a sample file. Where is the previous selected name? In the rows above from the same column or is it in the same cell, where you are making another selection?
You can send it by mail, or create a new ticket on Help Desk.
Catalin
William Petit
rand in Word you can also specify the number of lines and paragraphs if you enter both values in the brackets – =rand(5,3)
Which is 5 paragraphs of 3 lines…
Great for teaching or testing printers!
Mynda Treacy
Sure is, William. Thanks for sharing.
Mynda
Sarah
I do not understand. I wanted random from C1:C500. I put =Choose(Randbetween(62500000000, 1), C1:C500). It does not work. why not?
Catalin Bombea
Hi Sarah,
If you want that, you should use:
=INDIRECT(“C”&RANDBETWEEN(1,500))
Catalin
Afsar Mohiuddin Qhadri Syed
How to use the formula for =countifs( for the below file in the below format
1-Mar-14 Credit 100 2000 CHENNAI
2-Mar-14 Debit 200 1000 ANDHRA PRADESH
3-Mar-14 Credit 100 2000 BANGALORE
4-Mar-14 Debit 200 1000 HYDERABAD
5-Mar-14 Credit 100 2000 CHENNAI
6-Mar-14 Debit 200 1000 ANDHRA PRADESH
7-Mar-14 Credit 100 2000 BANGALORE
8-Mar-14 Debit 200 1000 HYDERABAD
9-Mar-14 Credit 100 2000 CHENNAI
10-Mar-14 Debit 200 1000 ANDHRA PRADESH
11-Mar-14 Credit 100 2000 BANGALORE
12-Mar-14 Debit 200 1000 HYDERABAD
13-Mar-14 Credit 100 2000 CHENNAI
14-Mar-14 Debit 200 1000 ANDHRA PRADESH
15-Mar-14 Credit 100 2000 BANGALORE
16-Mar-14 Debit 200 1000 HYDERABAD
17-Mar-14 Credit 100 2000 CHENNAI
18-Mar-14 Debit 200 1000 ANDHRA PRADESH
19-Mar-14 Credit 3300 4400 ANDHRA PRADESH
I need the above details in the given below format.
Region Sum of Debit-Count of Debit2-Sum of Credit-Count of Credit2
ANDHRA PRADESH 7100 20 23400 20
BANGALORE 1900 19 38000 19
CHENNAI 1900 19 38000 19
HYDERABAD 3800 19 19000 19
Grand Total 14700 77 118400 77
Request to help me how to use this type of formula.
=COUNTIFS($B$2:$B$78,”Credit”,$E$2:$E$78,$A81)
Afsar …
Catalin Bombea
Hi Afsar,
The formula you presented will count the rows where both criterias are true in the same time; the second criteria range refers to Region, (E2:E78), but the criteria points to a cell in column A: A81, which is a date, so it won’t work. Instead of A81, change that to “ANDHRA PRADESH”, or point to a cell that has the region name in it.
For adding the values, use =SUMIFS($D$1:$D$19,$E$1:$E$19,$E22,$B$1:$B$19,”Credit”), in E22 type the region name: ANDHRA PRADESH or any other region, or type directly in formula: =SUMIFS($D$1:$D$19,$E$1:$E$19,”ANDHRA PRADESH”,$B$1:$B$19,”Credit”)
If you need more assistance, please upload a sample workbook with your data structure, on Help Desk.
Catalin
Lana Gokey
I am trying to use the randbetween function to take a simple random sample of a data value set of 150 ages varying from 17 to 66. Note there are some ages in the set that are not represented. How do I get a sample of say 20 random ages. I have tried =choose(randbetween(2,151), a2, a3, a4, etc but this is not working.
Thanks
Catalin Bombea
Hi Lana,
Use =RANDBETWEEN(17,66) to get a value between those ages. If you want 20 random ages in that interval, you have to copy that formula in 20 cells, the function is not returning 20 values in 1 cell. Note that in those 20 cells, there may be duplicates, and all the rewsults will change every time excel recalculates the sheet. You can see that by pressing F9 to force a recalculation.
If you need more help, you can use the Help Desk: https://www.myonlinetraininghub.com/help-desk
Catalin
Yuri
Hi for CHOOSE and RANDBETWEEN, could you tell me what’s wrong with the 2nd formula, below? Thanks!
=CHOOSE(RANDBETWEEN(1,3), J36,J37,J38)
=CHOOSE(RANDBETWEEN(1,3),$J$36:$J$38)
Mynda Treacy
Hi Yuri,
The CHOOSE function requires you to enter the values separately, not as a range of cells. This is why your first formula works and your second doesn’t.
Kind regards,
Mynda.
Ovl
Is there a way to print these tips without having to print the extra side panels?
Philip Treacy
Hi Ovl,
you can use Print Friendly to do this
Regards
Phil
Mike Gallagher
The Print/PDF box does not show on my screen. I am using IE-11.0.16
Peace,
Mike G.
Philip Treacy
Hi Mike,
Sorry, we removed that link as it was causing the page to load very slowly. But you can visit Print Friendly to get a printer friendly version of the page if that’s what you want?
Regards
Phil
Chuck
I tried the choose(randbetween) function, but it is still repeating. How can I fix that?
Mynda Treacy
Hi Chuck,
Can you please show me your formula so I can understand why it’s not working as it should.
Thanks,
Mynda.
sadafkazmi
Dear sir,
thanx for the nice post. plz let me know how can we use this function in
MS Office 2003.
Regards,
S.SadafKazmi
Mynda Treacy
Hi,
It works the same in Excel 2003.
Kind regards,
Mynda.
mikeross
Thanks for an idea, you sparked at thought from a angle I hadn’t given thoguht to yet. Now lets see if I can do something with it.
Mynda
@mikeross. Great. I’d love to hear what you use RAND and RANDBETWEEN for your work.
Mynda.