O'Reilly Hacks
oreilly.comO'Reilly NetworkSafari BookshelfConferences Sign In/My Account | View Cart   
Book List Learning Lab PDFs O'Reilly Gear Newsletters Press Room Jobs  


 
Buy the book!
Online Investing Hacks
By Bonnie Biafore
June 2004
More Info

HACK
#9
Make Nested Functions Work in Excel
Getting nested functions to work is easy when you trick Excel features into mimicking functions and subroutines
[Discuss (8) | Link to this hack]

You're either a genius or a fool if you select an Excel spreadsheet cell, type an equals sign, pound out a multilevel nested function start to finish—and expect everything to work just the way you planned. It's easier to write code in even the most cryptic of programming languages. There, you often have a development environment that provides tips for function parameters; you can code and separately test subroutines and objects, or you can construct code stubs to gradually build up a working program. At first glance, Microsoft Excel appears to offer no more than the Insert Function command, which shows you the parameters only for a top-level function. However, you can simulate subroutines and stubs in out-of-the-way worksheet cells, and check the accuracy of your formulas every step of the way.

Suppose you want to create a formula that indicates whether you should change your diversification in growth, value, and bond investments, and you want to perform the following actions:

  • Calculate the formula only if the investment value is a valid number (not text or blank).

  • Display a warning when the investment is a valid number and is more than 25 percent of your total portfolio.

  • Use only half the investment value in the calculation if the investment is in a blend-style mutual fund, because the fund splits its investments between growth and value.

  • Extract the second word in the fund-style cell to determine the style.

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.

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:

  1. Select the test_diversification cell and select the formula without the equals sign in the formula bar.

  2. Press Ctrl-C to copy the formula to the Windows Clipboard. Be sure to press Esc to break out of edit mode in the test_diversification cell.

    TIP

    If you don't press Esc to stop editing, Excel will add every cell you select and every character you type to the formula in that cell.

  3. Next, replace the cell reference Investment with its cell address, C2. The resulting formula looks like this:

=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.


O'Reilly Home | Privacy Policy

© 2007 O'Reilly Media, Inc.
Website: | Customer Service: | Book issues:

All trademarks and registered trademarks appearing on oreilly.com are the property of their respective owners.