Formula In Name

JKP Application Development Services.

Microsoft Office Application Development

Formula In Name

Up • Introduction • Define • Use • Absolute/Relative • Context • Special Names • Formula In Name • Dynamic Names • XLM In Names • Arguments • Bugs Name Obj. • Conclusion

• •

Search

Zoeken

Go to	Ga Naar
Home Up

Donate	Donaties
Did you find something helpful on my site? Consider a donation! Heeft u iets gevonden waar u wat aan had? Overweeg dan een donatie!

Range Names In Excel

Introduction 1 2 3 4 5 6 7 8 9 10 Conclusion

A Step Further: A Formula In A Defined Name

Up until now, the names in this article referred to ranges of cells. In reality, a defined name in fact just holds a formula (like in a cell!) and you can use any of Excel's extensive list of worksheet functions in names. This opens up a world of possibilities!

Have a look at fig. 12. Here a name called ColumnSum has been defined, using cell B5 as the anchor cell. The principle of using relative references has been used here as well, as shown in a previous section. The formula for ColumnSum is:

=SUM(B$2:B4)

Or the sum of all cells as from row 2 down to the row immediately above the cell in which the name has been applied to, restricted to the same column as the name is used in.

Fig. 12: Formula in a name

So when you enter =ColumnSum into cell B5, in reality Excel will compute =SUM(B2:B4) for you (See Fig 13). Similarly when entered into cell D20, Excel calculates =SUM(D2:D19). The advantage of using this technique shows especially with complex formulas. Should you need to change anything to the logic of the formula, then it suffices to just change the formula in the defined name as opposed to having to update multiple cells.

Fig. 13: Named Formula in cell

Another nicety about named formulas, is that they behave as array formulas by default.

<< Previous Next >>

Subscribe in a reader