Documentation/How Tos/Using Names as a Reference to Formula Items

Who does not love a formula like this?

  =(E18*F25)/((AVERAGE(C23:C20)) + SUM(D18:D29))

Now, the math is fairly complex and just looking at it, you are not sure what it is referring to.

If you used names, which let you give more clear, descriptive names to parts of your spreadsheet, it could look like this.

  =(TotalSales*TotalMonths) / ((AVERAGE(2006Sales)) + SUM(SalesBonus))

That is a little clearer.

But how do you add the names? You can assign any name you want as follows:

1. Select one or more cells and choose Insert > Names > Define.
2. Type the name, click Add, and click OK.

Then you can create formulas like this.

File:Doc HowTo Rangenames.png

Sheet with range names

Note that you cannot do =Ement+Admit because those names refer to ranges. You cannot do =Ement+Admit because you cannot do =B17:B20+E17:E20 .

You have to create more names to refer to just the totals. Create other names for the total fields, for example the =SUM(Ement) total field, so that then you can type =TotEment+TotAdmit (Total Entertainment Expenses plus Total Admission Expenses).

There is a quicker way to get all your rows and columns named. Let us assume you have got this data.

File:Doc HowTo columnnames.png

Example

1. Select all the data and choose Insert > Names > Create.
2. In this case it makes sense to select the checkmarks for Top row and Left column since that's where the labels are.
3. Then click OK.

To look at the names created, choose Insert > Names > Define.

Now you can create totals. Note that when you are in a formula and you start to type a name, it will appear in the Formula field as "Prepaid_Taxes" does here. Press Enter to accept it so you do not have to type the whole name.

File:Doc HowTo typingnames.png

Typing formulas with names

As mentioned before, you will probably want to make fields to just refer to the totals. Select the cell and choose Insert > Names > Define. Type the name, for example TotBusExp for the cell G3 that displays the total of the Business Expenses. Then click Add, and click OK.

And then you can do something like this for the formula. (Yes, you might do a SUM(range) for this but let us say that you have got all these amounts, Bus exp and Salary etc. all over on different sheets or farther apart, so you would have to do the + instead)

File:Doc HowTo namesinformula.png

Calculating with names in formulas

and get a result.

File:Doc HowTo result.png

Result of the calculation

If you want some visual help figuring out where all those names come from, now that you don't have the cell references, you can choose Tools > Detective > Trace Precedents.

This How To comes from Solveig Haugland's Blog

Content on this page is licensed under the Public Documentation License (PDL).