As we’ve already seen, the RANDARRAY function can be used to generate random dates and times, which are numeric values. How can we generate random values that aren’t numeric? One way is to use this is to use the RANDARRAY function to generate random positions, then use the INDEX function to retrieve the values at these positions. In this worksheet, I have a list of 4 colors in the range B7:B10. In column E, I’ll enter a formula to create a random list of these colors. To start off, I’ll enter just the INDEX function. The array is B7:B10, and for row, we’ll want a number between 1 and 4. For example, if I enter 1, we’ll get “red”, if I enter 2, we’ll get “blue”, and so on. Now, we don’t want a hardcoded number here, we want a random number, and for this we’ll use RANDARRAY. The rows argument corresponds to the number of text values we want, so this value comes from cell C4. Columns is 1. Min is 1 and max is 4, since we have four colors in the list. Finally, the integers argument needs to be set to 1 or TRUE, because we want whole numbers. When I enter the formula, we get 20 random colors based on our list. This list is dynamic. So, if I change a color, we’ll see that change in the output. Now let’s improve things a bit. First, I’ll convert the colors to an Excel Table, and name the table “colors”. Then I’ll update the formula to use the table reference. This will make it easier to add new colors. I also need to change the max argument inside RANDARRAY. Instead of hardcoding the number 4, we want the actual count of colors. To get this, I’ll use the COUNTA function to count values in the table. Now I can easily add new colors, and they’ll be picked up by the table, and appear in the output. Finally, note that RANDARRAY generates a new list of colors each time a change is made to the worksheet. If I want fixed values that don’t change, I can copy the colors to the clipboard, then use paste special with values to overwrite the formulas with static values.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
