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:
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:
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:
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!
Download the Sample File
If you’re studying air traffic control too, or want to create other types of random scenarios, you can download the sample file from my Contextures website.
Go to the Sample files page, and in the Functions section, look for FN0039 – Create Random Scenarios.
The file is in Excel 2007/2010 format, and is zipped. There are no macros in the file.