Excel - How To Do A Random Number With No Repeats In Excel - Episode 1806
Microsoft Excel Tutorial: Generate 3 sets of the numbers 1-20 with no repeats in any set.
Welcome to the MrExcel podcast. In this episode, we will be discussing how to generate three sets of random numbers without any repeats. This question was sent in by gamezilla via KZread, and we are here to provide the solution.
To start off, we will use the =rand() function to generate 20 sets of random numbers. Next, we will copy this formula across to create three sets of numbers. However, we want to ensure that there are no repeats within each set. To do this, we will use the =rank() function. This function will rank each number in the first set and then compare it to the numbers in the remaining sets to ensure there are no duplicates.
To lock down the formula, we will press F4 twice, once to lock everything down and the second time to lock just the rows. This will allow the formula to move to columns B and C as we copy it to the right. By copying this formula throughout, we will get three sets of numbers with no repeats. While it is possible to get the same number in each set, it is highly unlikely and would require pressing F9 for the next 15 years.
Thank you for tuning in to this episode of the MrExcel podcast. We hope this solution helps you in your Excel endeavors. Don't forget to subscribe to our channel for more helpful tips and tricks. See you next time for another netcast from MrExcel.
Buy Bill Jelen's latest Excel book: www.mrexcel.co...
You can help my channel by clicking Like or commenting below: www.mrexcel.co...
Table of Contents:
(00:00) 3 Sets of Random No Repeats
(00:20) Generating Random Numbers
(00:40) Using RAND and RANK function
(01:17) Possibility of Repeats after 1 million tries
(01:40) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #randomdata
This video answers these common search terms:
how to do a random number with no repeats in excel
how to generate a random number in excel no repeats
how to make a random number generator excel no repeats
how to do a random number generator in excel with no repeats
Join the MrExcel Message Board discussion about this video at www.mrexcel.co...
Пікірлер: 8
This is cool! RANK is a new function for me. I've never needed this directly but I can see its use for generating bogus/random datasets--just like the gamezilla.
That is a great tip! I have never seen that combo of RAND and RANK before!
I've used RAND and RANDBETWEEN a lot. RANK is one that I've never used or thought to ever seek out.
I just love a simple solution!!
Coloured ( through conditional format)cell of that chart should automatically show project name I.e management. Thanks ..
Bill, once again a great video I have one question I have a conditionally formated gnatt chart of a project planner consists of fields as project topic , start date, no of days, end date (which is automatically calculated. And a conditionally formated gantt chart now my question is as soon as I enter a record in project name field it should be seen in chart on the first coloured cell for eg.. if my project name is management and the start date is 1st april to 10th april and on the first ... ctd
No. 3 in your first example?!
But I did see repeats, the