Suggestions for building and managing complex functions in Excel

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?

Solution 1:

Here's a bunch of main rules I use on my own and suggest to for staff during trainings:

1. Build complex formulas (especially IFs) part by part (or argument by argument), i.e. if you have a column of data you want to parse according to certain condition, start with simple =IF(LEFT(A1,2)="AB",1,0) - thus you're sure the condition works as expected and returns correct results for each type of values. Further you replace each argument by its own calculations (see below), but such way you're sure that any error / wrong result is NOT caused by wrong condition itself, otherwise errors may interfere and overall formula results are totally unexpected and extremely hard to correct.
2. Each argument is prepared in separate column as independent formula, and only if it works, it is merged to main formula (or megaformula as it is called in some Excel Bibles).
3. Use hardcoded values as intermediate solutions. E.g. if you're building a dynamic chart, range is usually calculated using OFFSET and COUNTA functions. So, firstly build your OFFSET using pure numbers, e.g. =OFFSET(A1,0,0,1,10), make sure it returns what you really want, and only then start building calculated part, e.g. =OFFSET(A1,0,0,1,COUNTA(A:A)-1).
4. Do not neglect formula evaluator (Ribbon Formulas > Evaluate Formula): for people who see it for the first time it's like a ray of light) Indeed, having correct syntax does NOT mean in general having expected / correct results, and that's like Excel debugger.

5. To improve readability of complex formulas, you may use new line separations (may be added via ALT+ENTER in formula bar) as well as extra spaces to make them look like a code with indents (all these are ignored when formula is evaluated):

   =IF(
       LEFT(A1,2)="AB",
          MONTH(TODAY()),
          MONTH(B1)
      )
   

I hope these suggestions will make life easier) Sure other answerers will add some more useful points.

Solution 2:

Generally, splitting larger formulas across columns is in my opinion best practices!

However, if you need to build larger formulas, there are a few things I'd recommend:

1. Use named ranges for constants - makes your formulas easier to read - better have =IF(A1>SalesThreshold,A1,0) than =IF(D2>Assumptions!$B$239,D2,0)
2. Use the Excel tables and the replacement of the normal references with the column names. This way, =IF(AND(YEAR(B2)=2013,C2="MyProduct",D2>SalesThreshold),D2,0) becomes =IF(AND(YEAR([Date])=2013,[Product]="MyProduct",[Sales]>SalesThreshold),[Sales],0)
3. If the formula is still complex, you can use the N() function for comments. The function will convert any text to 0, so you can have a function like this:

   =IF(
   N4>N20+N("A quick check"),
   N4^2+N("Nees to be squared because I cannot think of a better example"),
   N4/2+N("No idea what to comment here!"))
   

Hope that helps!