Browse DevX
Sign up for e-mail newsletters from DevX


Build this IF Function Parser to Simplify Debugging Excel Formulas

If you're tired of trying to decipher long Excel IF formulas displayed in a single line of code, try this convenient Formula Formatter add-in that displays such formulas neatly indented in a popup window.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Thanks for your registration, follow us on our social networks to keep up-to-date