Follow-up: Randomly select a day of the week in Excel

This exercise was originally posted at: http://maritimeorca0.blogspot.com/2016/09/randomly-select-day-of-week-in-excel.html

The Task

Create an Excel spreadsheet that includes a formula designed to pick a day out of the week at random using spreadsheet software such as Microsoft Excel.

You will need to use one of Excel's random functions to generate at least one random number as the basis of this exercise. That random number or perhaps a series of random numbers will then be needed to pick one of the seven days of the week (Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday).

My Solution

I decided to pursue multiple options for this challenge. I created four different formulas. My solutions have been included in an Excel file that can be found at: https://1drv.ms/x/s!AL3VXpGA-24u3UM

One thing that I prefer to do in spreadsheets is to keep tables that are being used for references separate from the work. I have a worksheet named Tables. In that, I created a table named tblWeekDays for the days of the week. I added headers showing that one column is for Values and the other is for the day. I also have another for random numbers, but I'm not yet ready to discuss that one. For the table's data, I added 1 through 7 for values and Sunday through Saturday for the days.

My formulas have been added to a worksheet predictably called Formulas. For each formula, I have put big and bold text to start each formula. I wrote the formula that shows a random day on the Random Day line. The formula was then copied and pasted to the Formula line. An apostrophe was added before the formula to tell Excel to treat the formula as text instead of a formula.

My first solution is simple. I used a VLOOKUP combined with a RANDBETWEEN function. I randomly pick a number from 1 to 7. From there, I check for the value in tblWeekDays and return the data from the 2nd column, which is the day of the week. Although the table is sorted by value, I still feel that it's good form to look for an exact match. You never know when someone is going to change the sorting on you. That's why I finished with the FALSE.

=VLOOKUP(RANDBETWEEN(1,7),tblWeekDays,2,FALSE) My second formula reduces how much of a table is required. This approach does not require the values table. It only looks at the Day column. The formula starts at the header for the Day column, then checks for the value at a cell in the row randomly picked from 1 to 7 below it in the same column (the final 0 indicates the change in the column). I selected the header in the formula by clicking on it. The table references should be easy to figure out, but there are other ways to handle this one. I could have easily used Tables!$B$1 instead. That would have made an absolute reference to the same cell that is currently occupied by the header. This also does not require that the table is actually identified as a table. Another option would have been to use the initial reference as Tables!$B$2. If we also changed the RANDBETWEEN function to look for a value from 0 to 6 instead of 1 to 7, this would allow us to enter the formula without bothering with the header. =OFFSET(tblWeekDays[[#Headers],[Day]],RANDBETWEEN(1,7),0)

The third option was deviating from my own advice. I wanted to show how this formula could be entirely self-contained. I used a CHOOSE function. A random number between 1 and 7 is used to select from the list of the days of the week.

=CHOOSE(RANDBETWEEN(1,7),"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday") My final formula took a different approach. Instead of using a single RANDBETWEEN function, I added random numbers to each day in the day of the week table. This was done by adding the Random column and adding a RAND function for each of them. There are ways that this approach can evolve that would not be possible with the previous formulas. Like my second formula, I used the OFFSET function with a start at tblWeekDays[[#Headers],[Day]]. Instead of using RANDBETWEEN to pick which day we are using, I looked for the largest of the random values by using the MAX function. Alternatively, MIN would be just as effective. I used the MATCH function to find where in the list of random values that exact (the final 0 in the MATCH function and the next-to-last 0 overall specifies an exact match) maximum value is located. From there, Excel will look down from the Day header as many rows as that value is located, and stay on the Day column. =OFFSET(tblWeekDays[[#Headers],[Day]],MATCH(MAX(tblWeekDays[Rand]),tblWeekDays[Rand],0),0)

Feel Free to Comment

Now that this exercise has concluded, feel free to share any thoughts that you had regarding results, your approach, and any other ideas that popped in your head regarding this exercise.I do not provide storage to those who visit my blog. If you would like to share any files, you are going to have to find your own online storage. At that point, you can provide links with your comments. I would like to point out that macros can create security risks. If possible, save all documents as a standard workbook (not macro-enabled) unless macros are vital to your solution. If you are going to download any solutions, make sure that you are not loading macros by default. If a spreadsheet includes macros, check the code before running them.

Upon completion of this exercise, please fill out the following form:
https://docs.google.com/forms/d/e/1FAIpQLSdF46VR38J_6T_VgZ7HtushaR7MY_BfjazYXKfkqM_BqVq6Zw/viewform?usp=send_form
Responses to the above form are collected and stored in a spreadsheet that can be viewed at:
https://docs.google.com/spreadsheets/d/1Zi2nkLCzclhwhB1mBug6t7UJKKLQsinPwHaVulmMmc4/pubhtml