Excel - How To Do A Random Number With No Repeats In Excel - Episode 1806
2.9 هزار بار بازدید -
11 سال پیش
-
Microsoft Excel Tutorial: Generate 3
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 YouTube, 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: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
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 https://www.mrexcel.com/board/threads...
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 YouTube, 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: https://www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...
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 https://www.mrexcel.com/board/threads...
11 سال پیش
در تاریخ 1392/07/22 منتشر شده
است.
2,901
بـار بازدید شده