Chapter 2: Natural Language Range Names

Overview

• How do I create a name for a range of cells based on a spreadsheet label and use that name in formulas?
• How do I use natural language range names to incorporate a relationship such as Month(t) Ending Inventory = Month(t) Beginning Inventory + Month(t) Production -Month(t) Demand in a spreadsheet formula?

Microsoft Excel 2002 and Microsoft Office Excel 2003 give you the ability to create 'natural language' range names based on labels already present in your spreadsheet. I'll illustrate the use of natural language range names with two examples.

 Note  This feature is available only in Excel 2002 and Excel 2003.

How do I create a name for a range of cells based on a spreadsheet label and use that name in formulas?

In the file NaturalRange.xls, shipments from three plants to four cities are listed. We want to find the total amount shipped from each plant and the total shipped into each city. Figure 2-1 shows the information in NaturalRange.xls.

Figure 2-1: Shipments from plants to cities.

To use the natural language range feature, choose Tools, Options. On the Calculation tab, select the Accept Labels In Formulas option. Clearing this option makes the natural language range names invalid.

Next we enter in cell F5 the formula SUM(Plant 1). Note that the total shipments from Plant 1 (573 units) have been tabulated. Excel sensed from the Plant 1 label in row 5 that you wanted to name the range B5:E5 Plant 1. This is amazing, but there's more! Copy the formula in F5 to the range F6:F7. In F6 you will see the formula SUM(Plant 2), and in F7 you will see SUM(Plant 3). You can see that Excel is smart enough to determine the 'natural' way to copy the formula in F5, and it adjusted the range names used in F6 and F7 accordingly. In a similar fashion, copying the formula SUM(City 1) from B8 to C8:E8 computes the total shipments into each of the four cities. This use of natural range names makes the logic of the spreadsheet much easier to understand.

Finally, suppose we want to enter the amount shipped from Plant 1 to City 1 into a cell. We could just use the formula =B5. Alternatively, we could use the formula =Plant 1 City 1. Excel now looks for the cell at the intersection of the natural language names defined by Plant 1 and City 1. Of course, this is the spreadsheet entry in cell B5.

How do I use natural language range names to incorporate a relationship such as Month(t) Ending Inventory = Month(t) Beginning Inventory + Month(t) Production -Month(t) Demand in a spreadsheet formula?

Often, spreadsheets contain modeling relationships that relate quantities at different points in time. For example, if we are trying to update our ending inventory each month, an appropriate formula is:

Month(t)Ending Inventory = Month(t)Beginning Inventory + Month(t)Production --Month(t)Demand
If we are updating a toy store's cash on hand at the end of every month, an appropriate formula is:

Month(t)Ending Cash = Month(t)Beginning Cash + Month(t)Cash Inflow -Month(t)Cash Outflow

Natural language range names make it easy for a person viewing a spreadsheet to see how this logic was included in the spreadsheet. The file NatRangeII.xls (see Figure 2-2) shows how to model the computation of monthly inventory levels with natural language range names. We begin month 1 with 100 units in inventory and produce 150 units each month. Monthly demands are given in column D of the spreadsheet.

 
Figure 2-2: Computing inventory with natural language range names.

In cell B6 we set month 2 beginning inventory equal to month 1 ending inventory by entering the formula =E5. Copying this formula to the range B7:B10 computes beginning inventory for months 3-6. We use natural language range names to compute ending inventory by copying from E5 to E6:E10 the following formula:

=Beg Inv + Prod - Demand

To see what this formula does, focus on any of rows 5-10. For example, to evaluate the formula in cell E5, Excel finds the number in row 5 from the column headed Beg Inv, adds this number to the number in row 5 from the Prod column, and then subtracts the number in row 5 from the Demand column. Copying this formula down to rows 6 through 10 now computes the correct ending inventory level for each month. Note that if we had copied =B5+C5-D5 down to E6:E10, few people would have understood the logic of our computations. The formula =Beg Inv+ Prod - Demand makes our logic more apparent.