Far too often, I've got a formula that wraps functions inside of functions inside of functions, and when some new condition requires that I wrap another function around it, I often find myself completely losing track of which parentheses go where, which function applies to which set of parameters and usually find myself debugging a statement for a half hour after my modification produces unexpected results.
What I've BEEN doing is to cut the function into its individual parts in a column...each row representing a single function, and when I'm satisfied that end result is what it needs to be, I copy each piece back into it's respective spot in the preceding cell until I have a completed, and hopefully working, function.
CONSIDER:
=IF((ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))),(IF((D2=(VLOOKUP(D2,$A$2:$A$9,1,0))),0,D2)),E2)
0 =IF((ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))),L17,E2)
TRUE =ISERROR((VLOOKUP(D2,$A$2:$A9,1,0))
0 =IF((D2=L18),0,D2)
the =VLOOKUP(D2,$A$2:$A$9,1,0)
It'd be great to be able to document inline or have something similar to a VBA popup ion which to edit formulae, but since that doesn't exist, I'd be interested to know what other techniques for effectively building complex functions you've found helpful. Thoughts?