Online Investing Hacks

Make Nested Functions Work in Excel

Getting nested functions to work is easy when you trick Excel features into mimicking functions and subroutines.

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