Create Random Scenarios in Excel

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.

randomscenario01

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.

randomscenario02

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.

randomscenario03

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.

randomscenario04

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

Create Random Scenarios in Excel

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.
______________

5 thoughts on “Create Random Scenarios in Excel”

  1. 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.

  2. 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?

Leave a Reply to Jim Cone Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.