Using Named Cells

Instead of using cell references, such as B24, which can make formulas quite inscrutable, assign descriptive names to individual worksheet cells, ranges of cells, formulas, or constants. Names are like variables and make formulas easier to read, as illustrated in .

Names are also more portable. You can reference a name in an Excel worksheet throughout the entire workbook, and move formulas to other cells or even to other worksheets within the workbook without changing the name references they contain. If you're going to use a value more than once, it's much easier to calculate that value in a named cell and refer to the name.

Creating Stubs

For logical functions, it's easy to build a stub to check your logic before you plug in calculations. For example, the Excel IF function uses three parameters: the test condition, the result when the test is true, and the result when the test is false, shown in .

You can type in a test condition along with hardcoded strings or cell references to show the true and false results, as shown in .

TIP

In this example, investment and test_diversification are name references to cells C2 and sheet2!A2, respectively.

Here's how the parameters work:

Test condition

For the first parameter, specify a condition test that checks if the investment is a number: ISNUMBER(investment).

Result if test is true

If the test condition is true (the investment is a number), you want to test for diversification. For this parameter, you simply type the name of the cell that performs the diversification test.

Result if test is false

If the investment cell doesn't contain a number, you want to display a warning. For this parameter, then, type the string: Investment isn't a number.

To test the formula, type the string Check diversification in the test_diversification cell. When you type a valid number in the investment cell, the action cell should display the string Check diversification. When you type a string or leave the investment cell blank, the action cell should display Investment isn't a number.

This formula is done! Because it works and refers to other cells, it won't change, regardless of future calculations. For example, you can replace the text in the test_diversification cell with the formula that actually tests your portfolio diversification. shows another example of simplifying logical tests using strings and cell references.

Creating Excel Subroutines

To create a true subroutine in Excel—one that you can call with parameters—you must use macros and Visual Basic for Applications (VBA). However, you can mimic the behavior of a subroutine using intermediate calculations in several cells, where each cell uses the results of other cells. By combining this divide-and-conquer approach with named cells, each intermediate calculation is easy to decipher and test.

TIP

You can nest functions only to seven levels in Excel. If you have a particularly deep nesting problem, you can sidestep this limitation by calculating portions of your problem in other cells.

The three components of the test for diversification show how this subroutine technique works. In this example, we'll develop the lower level tasks first:

1. Determine the fund style.

2. Calculate the amount of the investment to use.

3. Calculate the percentage that the investment represents of your total portfolio.

Hacking the Hack

If you plan to use the top-level formula more than once—for instance, to evaluate each holding in your portfolio—you won't want to name dozens of cells or create separate formulas for each application of the formula. Not only does it take too much time, it's mind-numbingly tedious as well. However, after you get your calculations working properly, it's easy to go back and paste the intermediate calculations into the top-level formula. You can then copy the complete formula to other cells. To incorporate subroutine calculations into a top-level formula, start at the top and work your way down, replacing cell references with the contents of the cell, as shown here.

The original top-level formula is:

=IF(ISNUMBER(Investment),test_diversification,"Investment isn't a number.")

You can replace the named cell references with the formulas in those cells or, for references to values, the cell address, as shown in the following steps:

=IF(ISNUMBER(C2),IF(InvestPercent> 0.25,"Check diversification",
    "Leave it alone"),"Investment isn't a number.")

At this point, you can repeat Steps 1 through 4 to replace the cell reference InvestPercent with the formula in that cell. Then, repeat Steps 1 through 4 until you either replace all cell references with their corresponding calculations or run out of nesting levels. If you replace every intermediate calculation, the diversification formula looks like the one in .

TIP

In , you can leave the Portfolio cell reference in place, because each holding calculation uses the total portfolio value.