TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
 Specialized Dev Zones Research Center eBook Library .NET Java C++ Web Dev Architecture Database Security Open Source Enterprise Mobile Special Reports 10-Minute Solutions DevXtra Blogs Slideshow

# 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.

 Jun 22, 2005
 Page 1 of 4

### WEBINAR:On-Demand

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<>"",
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
else
START_IF_03
IF(T10<>"" then
else
START_IF_04
IF(X10<>"" then
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
else
START_IF_08
IF(T10<>"" then
else
START_IF_09
IF(X10<>"" then
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.