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!

image

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

select random scenario

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.

______________

You may also like...

3 Responses

  1. Alan Murray says:

    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.

  2. Jim Cone says:

    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.

  3. @Jim, very creative! There must be some unusual options on your selection lists. ;-)

Leave a Reply

Your email address will not be published. Required fields are marked *