Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
    
+1 for ALT+ENTER. Another awesome tip!! -1 to SE for <kbd> </kbd> not working in comments.... :| –  dwwilson66 Feb 18 '13 at 20:55
    
@dwwilson66 use <kbd>, not <key> ;) happy to help! Alas comments have limited markdown - this will work for questions and answers. –  Peter L. Feb 18 '13 at 20:57
    
I can subscribe to all of your points, I apply them, too! +1 Only that I'd really discourage any mega formulas - they are such a headache to debug, no matter how well structured/commented... –  Peter Albert Feb 18 '13 at 21:01
1  
@PeterAlbert totally agree, that's why I recommend for beginners to use intermediate calculations: perhaps we loose in readability and free space on the sheet, however win in correctness) –  Peter L. Feb 18 '13 at 21:03
add comment

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!

share|improve this answer
    
N function is some of the best news I've heard in months. That is a sweet function. Thanks for the tip! –  dwwilson66 Feb 18 '13 at 20:44
    
+1 for named ranges and N, Peter!) –  Peter L. Feb 18 '13 at 20:53
    
Thanks! However, I really think that the Excel tables are the best Excel feature that even power users don't know/apply! (similar to pivot tables for beginners - so powerful, but they just don't know and have a hard live) –  Peter Albert Feb 18 '13 at 20:58
    
@PeterAlbert Agreed. If you don't find out about it on the street, it's only availbale buried in the documentation somewhere. And how many power users read manuals? :-) –  dwwilson66 Feb 19 '13 at 12:38
add comment

FormulaDesk is a new free tool designed to do exactly what you want. It formats and displays the formula in a nested, rolled-up way, with the result of each sub-portion displayed, enabling you to get a batter understanding very quickly.

[Disclosure: I am the author of FormulaDesk]

enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.