Excel Tip of the Day
Use a named formula to simplify calculations (Excel 97/2000/2001/2002)
If you've been using Excel for a while, you've probably learned to take advantage of named ranges. Named ranges provide an easy--and readable--way of referring to a particular range of cells when you create worksheet formulas. In a similar way, you can create named formulas for instances when you want to repeatedly refer to a calculated result without actually storing the value in a worksheet cell.
We'll illustrate the technique with a simple example. Open a worksheet and enter numbers in range A1:A5. Now, let's say that your worksheet will contain several formulas that refer to the sum of this range. Ordinarily, you might create a SUM formula in a cell and then refer to that cell in your other formulas. We'll create a named formula that sums the results instead.
First, choose Insert | Name | Define from the menu bar. In the Names In Workbook text box, enter "MySum" (without the quotes). Next, replace the entry in the Refers To text box with:
=SUM(Sheet1!$A$1:$A$5)
and click OK. You can now refer to MySum to incorporate the SUM result in other formulas. For instance, enter the formula:
=MySum/2
in a cell and Excel displays the sum of range A1:A5 divided by two.
(Content provided by Element K Journals)
(Note: Your browser is set to refuse cookies. As a result, you may frequently see previously-viewed tips)
Print
CloseCopyright © 2001 - 2025 MJ Technologies, LLC. All rights reserved.