Happy Formula Friday.
If you want to download the example Formula Friday workbook then you can do so here.
I like generating random data a lot of the time to test out work sheet solutions. I have been asked by one of my subscribers to show how to generate some random names from a list of names that are contained in a data set or table. We can do this with ease in Excel, by using both the INDEX and RANDBETWEEN functions together. What are we waiting for???.
Here is a same list of names in a data table. I want to randomly pick a name every time the formula is refreshed. I have 10 names in this small example.
The first part of the formula is using the INDEX function. This will return a value or a reference to a value from within a table or range. To demonstrate how INDEX works, if we wanted to return the 3rd value or name in our column, which is our list of 10 names then our formula would be as below, which correctly return Chris Hall as the answer.
We can therefore combine INDEX with RANDBETWEEN as we can ask Excel to choose a random row number instead of the 3rd value. It looks like this. See how RANDBETWEEN replaces 3 in the formula?. We ask Excel to give a random number between 1 and 10 which refers to the rows 1 to 10 in our data set.
Every time we hit return or F9 to refresh the formula we get a random name.
How cool is that?
Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.