This was exactly what I needed for my hobby. Thank you ComputerGaga For anyone who just wants to copy-paste the string, it's =INDEX(X#:Y#,RANDBETWEEN(1, Z)) where Z equals Y# minus X#. So if Index(A4:A19) then Z = 15 (19-4)
It's actually Z = [Y - X] + 1. If you leave it only as "Y-X", you get one less cell. To illustrate, if you have 3 cells, and you do RandBetween with the former calculation (3-1 = 2) you would get "RandBetween (1,2)". Meaning you'd get only 2 cells (the first and the second) out of the 3 you used. Therefore, it must always be Z = Y - X + 1. E.g., if you use cells 5 through 10, that would be "RandBetween (1,6)", and so on and so forth.
Great video!! I’m so glad that you mentioned how to stop the formula from running by selecting the calculation option. I learned something new - always a good thing! 😁
@@Computergaga - BTW, have you got a video about restricting the calculations of volitile functions like Rand() or RandBetween()? Basically I'm trying to create a sheet with lots of randomly generated numbers without them changing every time I click on a different cell. Essentially I'm after a button that pings out a bunch of random numbers. I know you can turn of automatic calculations in the formulas tab, but that stops every other calculation too.
Hi Lomax, I don't think I do and you are right in what you say. You can create a button to run the calculations, but you won't be able to (I believe) stop just those cells calculating automatically. So the best answer is probably not to use those formulas, and have the button run some macro code to do the rand and randbetween instead.
@@Computergaga - Turns out it was easier to get sorted than I expected. So here is the solution for turning off random calculations in Excel 2010. 1) Under the file tab, select options>formulas. Enable iterative calculation and set the maximum iterations to 1. This allows you to generate a circular reference without getting a warning message. 2) Insert a Check Box from the Developer Tab. 3) Right click on the Check Box & select Format Control>Control and then link the Check Box to a cell of your choice (A1 in this example). So in the “Cell Link” box, simply type “$A$1”. 4) In cell B1 type this “=IF(A1,RANDBETWEEN(1,100),B1)” THAT’S IT! Now whenever the Check Box is selected the value of A1 will be TRUE and the RANDBETWEEN function in Cell B1 will trigger. If the Check Box is not selected, the value of A1 will be FALSE and the value of B1 will reference itself (the last randomly generated number).
Hi...this is a big help. However, I can't figure out how to add two more columns. I have a 11 members, that i need to schedule 3 people each Sunday for Church duty. How can I randomly pick 3 people per week, and have 3 names per row.
Thanks! For silly nerdy reasons, I was trying to write an excel program that can generate a sort of pseudo-history. (Eg. King Oleg II ruled from 271 AD to 295 AD, was succeeded by Leonid, etc....) I was going to brute force it by generating a list of random numbers (taken from a website), assign each number to a name and then have a long if/then sort of setup. But I figured there had to be a more elegant way.
Very nice, extremely helpful. I was looking into software that would help me, we have a drug testing service and need to select randomly selected drug testing candidates here at All Better Lab in Harrisburg. Your information is priceless. Thank you!
This helped me alot. Thank you so much. I wanted to know if I had two students with the same name and surname on the list how would one tackle that. If we were using unique ID for example s230 s232 and s230 and one needed to iterate through each item randomly... how could that be done . Thanks again
Hi! Let's say I want to be able to add more values in column A and not be limited to values within certain cells, for example A2:A15, and still have it randomise one at a time, how would I do that? I'm also using Google spreadsheets if that's any help because this will be shared with others.
Not with a formula. You would need a macro alternative for that Elaine. I have a blog post on using VBA for this - www.computergaga.com/blog/pick-names-at-random-from-a-list-excel-vba/ This does not remove the name that would just requrie an extra bit of code, but if interested this is a start.
Thank you, Siddharth. In this example, there is only value so there would be no repeat. If you multiple, there are numerous methods available from RANDARRAY to creating rankings which I have done in a few of my videos on this channel.
Thanks for sharing this and making it so concise. Let's say I also had Random Age, Random Gender, Random Height, etc. Every time I hit F9, all the random variables refresh. I know how to create buttons and assign very basic macros to them. I tried a copy-paste macro and they all still refresh. Is there a way to isolate each category so only that refreshes when I push the respective button?
Would I be able to use this to create a random-fill field in a mail merge? I want the "letter" to choose a random name from a list every time the letter is run. Does that make sense? I'm assigning something random to each recipient.
Thank you. Yes, you could use a formula like this =INDEX(Table1[Names],RANDARRAY(3,,1,ROWS(Table1[Names]),TRUE)) RANDARRAY produces a list of random row numbers to extract from. The number 3 in it takes three names. This can be changed to 15 or linked to a cell with a value.
Hi Computergaga !!! Thanks for you really helpful video!!!!!! My question would be, "how to have an "exercise column" and you want to use your instructions to have a different (random) exercise on each line (so they need never repeat the same exercise)???" I mean, each column is a weekday. Each line represents a different exercise from a list "range". So, each line cell must have to have an exercise BUT they have to be a different exercise on the same day (same column). COLUMNS NAMES = Monday Tuesday Wednesday Thursday Friday Saturday Sunday ROWS NAMES = Jump Rope Seats Squat Pushups List of exercises (the exercises names origin) Now, randomize them into the DAYS. Do you know how to do that?
I think I would use a similar technique to the one I used in my lottery number generator video. Randomly generate a number. Use COUNTIF and RANK to make them unique integers. Then use VLOOKUPs in all the weekday cells to pull over an exercise.
I followed to the letter, instead of a random name* it says "#ERROR!" P.S. I did it in Google Sheets, and I was trying to use it as a random game picker?
How do you produce the random value if it is linked? It seems to only be generating the text version, even though where it is pulling from is hyperlinked.
great tutorial!, how do we make the randbetween picks from a random list equally. means the total count number of names repeated should match each others? any idea?
+Michael Hart Sounds like a task better suited to VBA. For a formula we would need 5 so one for each name with some conditional elements to ensure they don't draw the same name twice.
+Michael Hart As requested a video explaining how to extract 5 or any number of unique and random names from a list. Excel VBA code is also provided via the link to be copied and used ruclips.net/video/vgr9rDw1sRQ/видео.html
Of course! Various techniques including single formula and multiple helper columnd approaches. One formula is the below. This works on the idea of a list of names in a Name column of a Table named Table 1. It also returns 3 unique names. =TAKE(SORT(HSTACK(Table1[Name],RANDARRAY(ROWS(Table1[Name]),1,0,1,FALSE))),3,1)
That might be easier using a VBA solution. Or just randomise the 12, so that it changes the order for the shifts or whatever you are using it for. And then do again. So it is not really extracting 4 at a time or whatever, but you could get that effect.
What would I do if I wanted to select, say, 5 different names at once, so the "Random Name From List" would show for instance "Gita Patel David Jackson Karen Christie Bob Liston Harry Johnson" ? Naturally, in my case it would not be names, but rather notations used, but I need them randomly given to me. Thank you in advance
Thank you, but can you help me with the column and an error I'm getting? You see, I use the Dutch version so I had to look up the function translations and I have an ID column and an actual names column. The ID column is to remain untouched, but I want to pull names from the name column. My name clumn is B. I have a header and then 16 names, for a total of 17 cells in it. This is my formula and it's not taking it: =INDEX(Tabel1[Starter Extender];ASELECTTUSSEN(1;16);Tabel1[Starter Extender]) It says there is an incorrect value?
I have a list of 35 and they are reference no.s such as A0055, A0058 etc. I want to pick 20 out of 35 at random. You formula works, but some of the cells return #REF. I change the values again, and another cell gives #REF??? Can you pls help me.
Hi Sath, This link show how to extract multiples from a list using Excel VBA - ruclips.net/video/vgr9rDw1sRQ/видео.html This may provide a better solution. The error message you are currently getting indicates that a cell reference s not valid.
I have this video on picking multiple names from a list at random if that was what you need - ruclips.net/video/vgr9rDw1sRQ/видео.html. Choose how many and run the random pick.
The best way when you have a column to select is to format the range as a table. Then use the table reference to select the column. That is not always the way I show in my videos, because I want to demonstrate different techniques as not all spreadsheet set up by people are the same. But tables are best.
I have a video doing this with VBA - ruclips.net/video/vgr9rDw1sRQ/видео.html To do it with formulas would require more helper columns to test, and too much. More a programming task.
Say I wanted to pick a random name based on how many years the employee has worked at the company e.g. generate a random employee who has been at the company for less the 2 years, and print their name, obviously assuming that data is part of the population
This was exactly what I needed for my hobby. Thank you ComputerGaga
For anyone who just wants to copy-paste the string, it's =INDEX(X#:Y#,RANDBETWEEN(1, Z)) where Z equals Y# minus X#. So if Index(A4:A19) then Z = 15 (19-4)
It's actually Z = [Y - X] + 1. If you leave it only as "Y-X", you get one less cell.
To illustrate, if you have 3 cells, and you do RandBetween with the former calculation (3-1 = 2) you would get "RandBetween (1,2)". Meaning you'd get only 2 cells (the first and the second) out of the 3 you used. Therefore, it must always be Z = Y - X + 1. E.g., if you use cells 5 through 10, that would be "RandBetween (1,6)", and so on and so forth.
Great video!! I’m so glad that you mentioned how to stop the formula from running by selecting the calculation option. I learned something new - always a good thing! 😁
Thank you, Bella 😊
Much thanks! This is the only one working out for me between bunch of "just copy this formula and press enter" on many google articles
You're welcome. Glad it helped.
SO many videos, over complicate this simple question. Thanks for that easy and clear to follow
Thank you Gavin.
So helpful, thank you!
You are the daddy! There's a couple of others that go deep, but you just keep knocking 'em out of the park with simple, powerful tricks.
Thank you Lomax. Much appreciated.
@@Computergaga - BTW, have you got a video about restricting the calculations of volitile functions like Rand() or RandBetween()?
Basically I'm trying to create a sheet with lots of randomly generated numbers without them changing every time I click on a different cell. Essentially I'm after a button that pings out a bunch of random numbers. I know you can turn of automatic calculations in the formulas tab, but that stops every other calculation too.
Hi Lomax, I don't think I do and you are right in what you say. You can create a button to run the calculations, but you won't be able to (I believe) stop just those cells calculating automatically. So the best answer is probably not to use those formulas, and have the button run some macro code to do the rand and randbetween instead.
@@Computergaga - Turns out it was easier to get sorted than I expected. So here is the solution for turning off random calculations in Excel 2010.
1) Under the file tab, select options>formulas. Enable iterative calculation and set the maximum iterations to 1. This allows you to generate a circular reference without getting a warning message.
2) Insert a Check Box from the Developer Tab.
3) Right click on the Check Box & select Format Control>Control and then link the Check Box to a cell of your choice (A1 in this example). So in the “Cell Link” box, simply type “$A$1”.
4) In cell B1 type this “=IF(A1,RANDBETWEEN(1,100),B1)”
THAT’S IT!
Now whenever the Check Box is selected the value of A1 will be TRUE and the RANDBETWEEN function in Cell B1 will trigger.
If the Check Box is not selected, the value of A1 will be FALSE and the value of B1 will reference itself (the last randomly generated number).
Nice work Lomax.
thanks man, using my spreadsheet as flashcards and wanted to test myself out of order :D
Awesome!!
come here to do the same
=INDEX(A2:A13,RANDBETWEEN(1,12))
Thank you for explaining this so simply! It was very easy to understand.
You're very welcome! Thank you for your comments.
Hi...this is a big help. However, I can't figure out how to add two more columns. I have a 11 members, that i need to schedule 3 people each Sunday for Church duty. How can I randomly pick 3 people per week, and have 3 names per row.
Thank you! But how do you prevent repeated names?
Assign each name a random number using =rand() for the cell next to the name and then sort by that
Thank you for this tutorial video. I really appreciate it. It is very useful for you.
You're welcome.
Worked beautifully, thank you!
You're welcome Jonathan.
Outstanding. Thank you in 2023.
Thank you 😊
Just what I needed and explained it easily and quickly. Thanks.
Thank you Kauri.
Just what I was looking for, thanks!
You're welcome.
Thanks, one of us had to deliver the presentation and no one wants to do it, so this will help🤣
Thanks for sharing! There are so many applications for this type function can apply.
You're welcome. Thank you.
Can you tell me how to get random names based on weighted probability such as the number of raffle tickets holding?
well explained and straight to the point. good video.
Thank you 😊
Thanks! For silly nerdy reasons, I was trying to write an excel program that can generate a sort of pseudo-history. (Eg. King Oleg II ruled from 271 AD to 295 AD, was succeeded by Leonid, etc....) I was going to brute force it by generating a list of random numbers (taken from a website), assign each number to a name and then have a long if/then sort of setup. But I figured there had to be a more elegant way.
You're welcome Phlebas. Thank you for watching.
Very nice, extremely helpful. I was looking into software that would help me, we have a drug testing service and need to select randomly selected drug testing candidates here at All Better Lab in Harrisburg. Your information is priceless. Thank you!
You're very welcome.
Hi Alan.. just came across this video. Nice technique to pick a name at random from a list. Thanks for the insights. Thumbs up!
Thank you Wayne.
Sir for multiple columns (from Table ) am not able to apply the above formulation... Plz suggest how to do that. 🙏
This helped me alot. Thank you so much. I wanted to know if I had two students with the same name and surname on the list how would one tackle that. If we were using unique ID for example s230 s232 and s230 and one needed to iterate through each item randomly... how could that be done . Thanks again
Wow. This was so helpful.
I really appreciate this
Great! Happy to help, Denisher.
Hi! Let's say I want to be able to add more values in column A and not be limited to values within certain cells, for example A2:A15, and still have it randomise one at a time, how would I do that? I'm also using Google spreadsheets if that's any help because this will be shared with others.
This is super helpful!
Thanks for the thorough tutorial !
You're welcome, Brendon.
I have a dynamic list, meaning column A always changes in size(+- rows), how can I make it look at just what it's populated?
Thank you
You're welcome
Amazing
very useful. tx :-)
You're very welcome Julien.
Great video! Thanks so much for the tutorial - works great! Is it possible to remove the name from the list after it has been chosen?
Not with a formula. You would need a macro alternative for that Elaine.
I have a blog post on using VBA for this - www.computergaga.com/blog/pick-names-at-random-from-a-list-excel-vba/
This does not remove the name that would just requrie an extra bit of code, but if interested this is a start.
SIMPLE & BEST VIDEO...BUT WHAT ABOUT VALUES THAT ARE REPEATING AGAIN ?
Thank you, Siddharth.
In this example, there is only value so there would be no repeat.
If you multiple, there are numerous methods available from RANDARRAY to creating rankings which I have done in a few of my videos on this channel.
Thanks noted buddy❤️
Thanks for sharing this and making it so concise. Let's say I also had Random Age, Random Gender, Random Height, etc. Every time I hit F9, all the random variables refresh. I know how to create buttons and assign very basic macros to them. I tried a copy-paste macro and they all still refresh. Is there a way to isolate each category so only that refreshes when I push the respective button?
This! Has anyone found an answer to this yet?
Excellent video mate, thank you!
Thank you very much 👍
Great video! Thanks 🙂
Thanks, Matthew.
Thanks
You're welcome.
Would I be able to use this to create a random-fill field in a mail merge? I want the "letter" to choose a random name from a list every time the letter is run. Does that make sense? I'm assigning something random to each recipient.
What if the names was also in the B Column, i.e. from A2:B13? Then what adjustments do we have to our formula to select a random value from A2:B13?
Very helpful, thanks! Is it possible to pull more than one? For example pull 15 names from a list of 400 in one go?
Thank you. Yes, you could use a formula like this =INDEX(Table1[Names],RANDARRAY(3,,1,ROWS(Table1[Names]),TRUE))
RANDARRAY produces a list of random row numbers to extract from. The number 3 in it takes three names. This can be changed to 15 or linked to a cell with a value.
thank you so much,
Welcome 😊
Let's say I put an x beside some names in column B, and I want the function to only pull from that selection, how would I do that?
This video saved the day! =)
Excellent! 👍
BIG THANKS ! VERY USEFUL!
You're welcome Amit, thank you.
Thanks man
You're welcome.
Super Easy Thank you!!!!
You're welcome, Erica.
Great video! Thanks for the help!
Thanks Stephanie, your welcome.
Is there a way to weight particular elements of the list heavier than others?
Have you found the answer to this?
Soooooo helpful thank you so much
Glad it helped, Mike.
I have a list that has numbers 101-136. some of the cells contain 0. How do i use this formula to random select a number grater than 0?
thanks bro for uploading this video we learn from this video how work excel easily and it is use random Excel formula
You're welcome Saiful.
PLEASE HOW CAN I WITH PRECISION FOR EXAMPLE IN OUR COUNTRY WE HAVE FOURTH DAYS PLAYING GAME PROGAMME EVERY DAY MY GENERATOR?
Thanks, very useful!
Great! You're welcome, Valerene.
Thank you My dear, It was a great help.
Glad it helped!
Hi Computergaga !!!
Thanks for you really helpful video!!!!!!
My question would be, "how to have an "exercise column" and you want to use your instructions to have a different (random) exercise on each line (so they need never repeat the same exercise)???"
I mean, each column is a weekday.
Each line represents a different exercise from a list "range".
So, each line cell must have to have an exercise BUT they have to be a different exercise on the same day (same column).
COLUMNS NAMES = Monday Tuesday Wednesday Thursday Friday Saturday Sunday
ROWS NAMES =
Jump Rope
Seats
Squat
Pushups
List of exercises (the exercises names origin)
Now, randomize them into the DAYS.
Do you know how to do that?
I think I would use a similar technique to the one I used in my lottery number generator video. Randomly generate a number. Use COUNTIF and RANK to make them unique integers. Then use VLOOKUPs in all the weekday cells to pull over an exercise.
Nice video! very helpful.
Thanks Sergio.
I followed to the letter, instead of a random name* it says "#ERROR!" P.S. I did it in Google Sheets, and I was trying to use it as a random game picker?
Thank you its really useful
Great to hear Mariam, thanks.
According to the comments this works! But could you make one for html in something like sublime?
Do you know if there is a way to start on a name you wanted?
How do you produce the random value if it is linked? It seems to only be generating the text version, even though where it is pulling from is hyperlinked.
Hello Jacob, I'm looking for the answer to the same question, I wonder if you found an answer to your question?
If Don't want a same name twice.then what should I do?
Sometime I get a '0' when i use this in the option. Can you please explain why this happens?
A 0 sounds like it is return a value form a blank cell. I would check the range used in the formula.
great tutorial!,
how do we make the randbetween picks from a random list equally. means the total count number of names repeated should match each others? any idea?
Thank you. So you want to randomise the list. I would generate random numbers in an adjacent column and sort them.
Thanks mate!
You're welcome. Thank you.
Nice
Thank you Kayamat.
nice video
Thank you, Farhad.
good video, One question... how can you pick random names, lets say 5, without duplicates. preferably using randbetween
+Michael Hart Sounds like a task better suited to VBA. For a formula we would need 5 so one for each name with some conditional elements to ensure they don't draw the same name twice.
+Computergaga im not familiar with vba at all. can you do another video explaning it?
+Michael Hart As requested a video explaining how to extract 5 or any number of unique and random names from a list. Excel VBA code is also provided via the link to be copied and used ruclips.net/video/vgr9rDw1sRQ/видео.html
I used this as a random topic picker cause the online roulette didn't work on my work computer
Is it possible to make it without repeat of names?
Of course! Various techniques including single formula and multiple helper columnd approaches.
One formula is the below. This works on the idea of a list of names in a Name column of a Table named Table 1. It also returns 3 unique names.
=TAKE(SORT(HSTACK(Table1[Name],RANDARRAY(ROWS(Table1[Name]),1,0,1,FALSE))),3,1)
Is there a way to use this formula and make sure that all 12 names are cycled through before repeating them?
That might be easier using a VBA solution. Or just randomise the 12, so that it changes the order for the shifts or whatever you are using it for. And then do again. So it is not really extracting 4 at a time or whatever, but you could get that effect.
if i put number, name should be come automatically in google sheet, how ?
Is there any way to do this without picking a name more than ounce?
I have a video doing this with VBA - ruclips.net/video/vgr9rDw1sRQ/видео.html
Hello, your video is very hard to see maybe you can play this formula through to me, it might help me with the problem I am facing.
And now for apple numbers :D
What if you don't want them to repeat?
Kleiner Tipp für alle die Excell auf Deutsch haben. Dort heisst es "Zufallsbereich" ;).
What would I do if I wanted to select, say, 5 different names at once, so the "Random Name From List" would show for instance "Gita Patel David Jackson Karen Christie Bob Liston Harry Johnson" ? Naturally, in my case it would not be names, but rather notations used, but I need them randomly given to me. Thank you in advance
This video shows how to select multiple names from a list at once ruclips.net/video/vgr9rDw1sRQ/видео.html
hello, what will be the formula if its a row instead of a column
If the names are in a row, range A2:E2. The formula would be =INDEX(A2:E2,1,RANDBETWEEN(1,5))
So helpful. 🫶🏻🫶🏻🫶🏻
Great! 😊
Thanks so much!! :-)
My pleasure 👍
Thank you, but can you help me with the column and an error I'm getting? You see, I use the Dutch version so I had to look up the function translations and I have an ID column and an actual names column. The ID column is to remain untouched, but I want to pull names from the name column.
My name clumn is B. I have a header and then 16 names, for a total of 17 cells in it.
This is my formula and it's not taking it: =INDEX(Tabel1[Starter Extender];ASELECTTUSSEN(1;16);Tabel1[Starter Extender])
It says there is an incorrect value?
I would remove the last bit of the formula and just use =INDEX(Tabel1[Starter Extender];ASELECTTUSSEN(1;16))
I have a list of 35 and they are reference no.s such as A0055, A0058 etc.
I want to pick 20 out of 35 at random. You formula works, but some of the cells return #REF. I change the values again, and another cell gives #REF??? Can you pls help me.
Hi Sath,
This link show how to extract multiples from a list using Excel VBA - ruclips.net/video/vgr9rDw1sRQ/видео.html
This may provide a better solution. The error message you are currently getting indicates that a cell reference s not valid.
If I were to pick a random 3 names from the list, how could I edit the function so no names repeat?
I have a video on that here Gary - ruclips.net/video/vgr9rDw1sRQ/видео.html
sir I have to adjust the attendance random like a,,,,,b,,,c,,,g how
is there any possible way to do this for multiple cells at once without using the same name twice in a row?
I have this video on picking multiple names from a list at random if that was what you need - ruclips.net/video/vgr9rDw1sRQ/видео.html. Choose how many and run the random pick.
Having same problem. Did you find the solution
What do you do when you have a Colom you need to select. Not all selections are as simple as this one. You need to explain the complete formula!!!
The best way when you have a column to select is to format the range as a table. Then use the table reference to select the column.
That is not always the way I show in my videos, because I want to demonstrate different techniques as not all spreadsheet set up by people are the same. But tables are best.
how can we select 5 out of 13 sir??
please tell how do we accomplish this same task without a single chance of any name getting repeated. please tellllll.
I have a video doing this with VBA - ruclips.net/video/vgr9rDw1sRQ/видео.html
To do it with formulas would require more helper columns to test, and too much. More a programming task.
Say I wanted to pick a random name based on how many years the employee has worked at the company e.g. generate a random employee who has been at the company for less the 2 years, and print their name, obviously assuming that data is part of the population
I keep trying to make a bunch of cells like this to choose Random names but then excel puts a 0 in red. Can someone help?
I have a video on picking multiple names from a list - ruclips.net/video/vgr9rDw1sRQ/видео.html
What if i want to pick from two lists.
It doesnt work for me dunno why...
I watch that because I was trying to pick a name
But what if you have more than one column in your table? You skipped over that bit
Once a name is returned. Lookup formulas (VLOOKUP, INDEX/MATCH) could be used to return data from other columns.
nevermind I used the wrong "as you called it - Bracket"
Good work fixing your formula Fonzy.
It doesn't work for me. I keep getting problems with the formula
Double check what you have compared to the video Fezury. There must be something different.
I found the solution. Apparently I cannot use a comma, but I have to use ;
Ah yes. In most of Europe the semi colon is used because the comma is used as a decimal separator.
Yes that explains a lot. Thanks anyways
You're welcome, thanks Fezury.
Explicit