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. ...
Get Online Investing Hacks now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
