Build this IF Function Parser to Simplify Debugging Excel Formulas

icrosoft Excel is a powerful tool for writing user interfaces for spreadsheet applications. Apart from the ability to display information in a tabular form, Excel has scores of functions capable of addressing most calculation needs—and if those aren't sufficient, you can create your own functions, because Excel has Visual Basic for Applications (VBA) embedded in it.

One powerful function that shipped with Excel is the IF function. The IF function is very similar to the IF statement in most programming languages, giving applications a way to choose one of several options. However, unlike the IF statement, you can not add comments to an IF function, because comments aren't permitted within function calls in worksheet cells. Therefore, it can be very difficult to understand a complex nested IF function.

Moreover, a programming language lets you indent lines of code, which helps make it much easier to read and comprehend what that code does. The worksheet IF function does not allow indentation.

Finally, you can nest worksheet IF functions, so it's easy to end up with a formula that's difficult to understand. Here's an example:

   =IF(price_br="BID",IF(B10<>"",IF(Q10<>"",
   (AD10-Q10),IF(T10<>"",(AD10-T10),IF(X10<>"",
   (AD10-X10),IF(P10="N"," Hello","No 
   Hello")))),""),IF(B10<>"",IF(Q10<>"",(AD10-
   Q10),IF(T10<>"",(AD10-((U10-T10)/2+T10)),
   IF(X10<>"",(AD10-((Y10-X10)/2+X10)),
   IF(P10="N",("Hello"),"No()  Hello")))),""))

Lest you think I simply made this formula up as a "straw man" example, I didn't. I saw it in a real spreadsheet in a real financial institution (although I did change some literal strings to disguise it).

If you are in a pressure situation where you need to debug a spreadsheet formula, you will yearn for a tool to help simplify reading and understanding the code. Excel does give you some minimal help by color-coding the opening and closing braces, but the Formula Formatter tool discussed in this article goes several steps further. After you install the Formula Formatter, right clicking on any cell containing an IF function allows you to view the formula in a nicely-formatted manner, and with any nested IF functions neatly indented.

The IF function examines a worksheet formula and converts it into pseudo code, displaying the function in an easy-to-understand form. For example, after parsing and formatting the IF function, the formula shown earlier looks like this:

   START_IF_00
     =IF(price_br="BID" then 
      START_IF_01
      IF(B10<>"" then 
      START_IF_02
       IF(Q10<>"" then 
        (AD10-Q10)
        else 
       START_IF_03
        IF(T10<>"" then 
         (AD10-T10)
         else 
        START_IF_04
         IF(X10<>"" then 
          (AD10-X10)
          else 
         START_IF_05
          IF(P10="N" then 
           " Hello"
          else 
           "No Hello")
        END_IIF_05
        )
       END_IIF_04
       )
      END_IIF_03
      )
     END_IIF_02
     else 
      "")
     END_IIF_01
       else 
     START_IF_06
       IF(B10<>"" then 
      START_IF_07
       IF(Q10<>"" then 
        (AD10-Q10)
        else 
       START_IF_08
        IF(T10<>"" then 
         (AD10-((U10-T10)/2+T10))
         else 
        START_IF_09
         IF(X10<>"" then 
          (AD10-((Y10-X10)/2+X10))
          else 
         START_IF_10
          IF(P10="N" then 
           ("Hello")
          else 
           "No()  Hello")
        END_IIF_10
        )
       END_IIF_09
       )
      END_IIF_08
      )
     END_IIF_07
      else 
       "")
    END_IIF_06
    )
    END_IIF_00

As you can see, while still complex, it's far easier to read than the original packed one-line version.

Next Page

---

• Download The Code for this Article

---