Question for the Pros

  • Thread starter Thread starter Flamikey
  • Start date Start date
F

Flamikey

When you are writing complex nested formulas, do you type the formula
directly into the formula bar or do you use the function wizards? The
reason I ask is that I always mess up the syntax when I dont use the
wizard for complex formulas. I don't know if using the wizard is a
smart thing or if it is making me lazy and dependent.
 
Hi Flamikey!

You might find that Excel 2002 and above Function ToolTips make life a
lot easier. It's on by default but you can use the checkbox in Tools >
Options > General to turn it on and off. It does sometimes get in the
way of editing but you can move it out of the way.

In general terms, I use John Walkenbach's "megaformula" technique for
complex formulas.

He covers it in Excel 2003 Formulas and also at:

http://j-walk.com/ss/excel/usertips/tip079.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Hi Flamikey,

Essentially do what works for you.

From that, you will evolve your own 'best' method.


That said, you may actually find a User Defined Function even better.

While some may find complex formulas/formulae a rewarding challenge,
the ease with which a UDF can be audited and edited frequently makes
them a better option.

Explore and Enjoy

David
 
Same as Norman, I use the way John describes it. Start with a formula that
works, albeit it is full of temporary hardwired values, and then create other
formulas to replace the hardwired values and just copy / paste those formulas
into the initial formula, replacing the hardwired values.
 
While some may find complex formulas/formulae a rewarding challenge,
the ease with which a UDF can be audited and edited frequently makes
them a better option.
Click to expand...

Heartily agree with that statement, it also can make it easier to understand
by using sensible UDF names.

Another way that I also us is to define workbook names that cover parts of
the complex formula, especially if it is repeated in the formula (you know
the sort of thing, IF(ISNA(some test), "", some_test))). This also has a
documentary function, but unlike the UDF, the only variable part is the cell
it is defined in, there is no way (unless someone knows better) to pass
other variables to a workbook name..

Bob
 
Back
Top