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!
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 xlsx format, and is zipped. There are no macros in the file.
______________
Love the protein and side dishes idea. May have to implement it.
Been doing a similar thing alot lately with my sports fixture list and cup draw planners.
Debra,
I use something similar to occasionally create procedure names.
It can be real time waster if you aren’t disciplined.
Currently in use is “Breadcrumb_Waggles” for some public code postings.
@Jim, very creative! There must be some unusual options on your selection lists. 😉
Hey Debra,
Found this in the archives and its exactly along the lines of what Im trying to do for a scenario generator for an aviation dispatch training class. Is it possible that depending on what you select from List_1 only applicable items from List_2 will be options? For example, in List 1 if I select a Turboprop plane for my scenario, in List 2 I dont want it to generate a city pair from the US-China – I only want to see applicable city pair routes for that type of aircraft. Am I making it too difficult?
Kailey, thanks for your question, and I’ve uploaded a new sample file on my site:
https://www.contextures.com/excelfiles.html#FN0059
In that file, the Destination list depends on which aircraft you selected.