# Create Random Scenarios in Excel

My son is in an Air Traffic Control course, and there’s lots of information to memorize. Directions have to be given in a very specific sequence, or the pilots don’t respond.
Apparently, you can’t say, “Hey dude, just put it down anywhere.” No, you have to address the aircraft correctly, and specify an apron and refer to a valid destination. Or something like that!

### Create Practice Scenarios

Anyway, to help himself memorize all this information, my son asked if Excel could create random scenarios, with an aircraft, apron and destination. Well, of course Excel can do that!
Even if you’re not preparing to direct giantÂ  airplanes in and out of the sky, you might find some uses for this workbook. For example, you could change the options to Protein, Side Dish 1, and Side Dish 2. Then, select tonight’s dinner items with a single click.
Hmmm…What other types of scenarios would be useful?

### Create the Lists of Options

To create the scenarios, I started by naming the worksheets â€“ List_01, List_02 and List_03. Those generic names will make it easier to use the workbook for a different purpose later.
On the List_01 sheet, I added the heading “Aircraft”, and entered a list of 20 fake aircraft names.

On List_02, I created an Apron list, and on List_03, there’s a list of Destinations.

### Name the Lists

Next, I created a dynamic named range for each list, so he can add or remove items from the list.

The definition for the List01 range is:
=OFFSET(List_01!\$B\$1,1,0,COUNTA(List_01!\$B:\$B)-1,1)
Similar names were created for List02 and List03.

### Count the List Items

The first sheet in the workbook was named Scenarios. In row 7, I created three boxes, where the random selections will be shown.
In row 8, I used the COUNTA function to count the number of items in each named range.
Here is the formula in cell B8, which counts the items in List01:
=COUNTA(List01)
Then, I created similar formulas in C8 and D8, to count the items in List02 and List03.

### Create the Scenario Cells

In the boxes in row 7, I used the RANDBETWEEN function to pick a number between 1, and the number shown in row 8. There are 20 items in the Aircraft list, so RANDBETWEEN will pick a number between 1 and 20.
NOTE: In Excel 2003, and earlier versions, install the Analysis Toolpak if you want to use the RANDBETWEEN function.
The INDEX function then returns an item from the list, using that random number as the list row number.
Here is the formula in cell B7:
=INDEX(List01,RANDBETWEEN(1,B8))
There are similar formulas in C7 and D7, to pull random items from List02 and List03.

### Test the Scenario Selector

Finally, I added a heading to the worksheet, and a note that explains how to select new items.

• Press the F9 key to recalculate, and see new items

Rows 8 and 9 are hidden, to keep the worksheet clean and clutter-free. You don’t want to distract the air traffic controller.
I sent the file to my son, and it’s exactly what he wanted. His only complaint is that he wishes he had thought of it earlier!