More ways to generate random Numbers in Excel

In order to properly assign random identification numbers to those who contributed specific sets of data. I truly wanted randomly computer-generated numbers not just a 1-10 count. However, I did not know how to ask Excel to do this for me, so I consulted the internet. I googled “randomly generated numbers excel” and got a few promising articles and set to work learning. One of the best videos I found was from a youtuber known as Doug H. who specializes in excel and its functions, he is amazing! What most of the articles asked was to use the (=RAND) command which I found worked perfectly to generate a single random number, however I needed a lot more. Since the function needed a number minimum and maximum, I went with the classic 1-100; (=RANDBETWEEN(1,100)).

I got my labels of data sets all lined up going down column ‘A’ and then selected all of the adjacent column ‘B’ and entered the command at the very top and then dragged the command down the ‘A’ column to assure that the command would follow through for every corresponding ‘A’ value.

The command function did work, however, it contained repeat numbers. I initially thought this may have been because the number window, 1-100, was too narrow. But when I expanded it, it still contained repeats and when I repeatedly generated the whole list it contained repeats. Some articles I later encountered said that the (=RANDBETWEEN) function has a high duplicate probability. So, I switched the values back and then researched other ways to ensure that there were no repeats.

That’s when I started to gain less headway. So, instead of toying with it further I transferred the non-duplicate labels and their values to the adjacent columns of ‘D’ and ‘E’. I copy pasted the labels and typed in the random identifications. I typed them because the (=RANDBETWEEN) and (=RAND) functions still function so if I were to copy paste it, I would get a different value. Then, I singled out the specific sets who currently had duplicate numbers and reentered the function until it spit out a number that was not a duplicate. After it was all said and done, I then reassembled the list with the appropriate random IDs, and we were in business!

Although I would not describe my method as the best one it worked well for me and achieved the results I liked. If you are interested in doing this as well, I have included the links to the articles and videos I consulted below.

Excel Random Number Generator by Barb Henderson

Trump Excel (not what you think, trust me)

Excel Tips from EDUCBA (Free Stuff)

Excel Easy for Random Numbers

Leave a Reply

Your email address will not be published. Required fields are marked *