Taming Nested IFs

A bane of using the IF function is that often it cascades with one or more other IF functions occurring in its arguments. In a worse-case scenario, these IF functions can cascade many levels deep. Debugging, or even constructing, these formulas can be very challenging. FormulaForge helps users construct such formulas by allowing them to locally replace the Boolean conditions in the first (logical test) argument of IF functions with an identifying string and by providing a panel that shows the Boolean state of all parent logical tests for any subexpression contained within an IF function’s second or third arguments.

The formula in cell B3 below computes a string that describes the location of a point in a Cartesian space. The X and Y coordinates of the point are contained in cells B1 and B2, respectively.

Nested IF Excel Window

The location is constrained to be in a rectangular area as shown in the image below, that is, having an X value between 0 and 9, inclusive, and having a Y value also between 0 and 9, inclusive.

quadrants

 

Given values in cells B1 and B2 for the X and Y coordinates of a point in this Cartesian space, the formula will provide one of five strings: “Values out of range,” “Lower left quadrant,” “Upper left quadrant,” “Lower right quadrant,” or “Upper right quadrant.”

The image below shows Formula Forge’s central panel with cell B3 selected. The nested IF structure of the formula in that cell is clearly shown in the Tree View.

FormulaForge Central Panel

 

The image below shows Formula Forge’s central and right panels with the second argument of the last IF function (“Lower right quadrant”) having been highlighted by clicking that argument in the Tree View. This click highlights the argument in all views. It also makes a list in the Logic Regions area of the logical test argument, along with the value, of every IF function above the cell’s formula in the Tree View. In this example, one can see that the first logical test, AND(B1 >= 0, B1 <= 9, B2 >= 0, B2 <= 9), which ensures that X and Y are in range, is true. Likewise, one can see if the point is in the lower right quadrant because B1 <= 4 is false and B2 <= 4 is true.

FormulaForge Central and Right Panels

 

This is very handy, but it would be handier if the texts of the logical tests were replaced with descriptive strings. FormulaForge provides the capability to do this. In the image below of the full FormulaForge window, the Scrapbook area in the upper portion of the left panel assigns a descriptive name to each important logical test.

These names are the following:
“in_range” is AND(B1 >= 0, B1 <= 9, B2 >= 0, B2 <= 9)
“left_half” is B1 <= 4
“lower_half” is B2 <= 4

In the image below, these names have replaced the text of the various logical tests in all views in the central panel. With these descriptive names, it is much easier to understand the logical tests in the entire formula. Furthermore, the Logical Regions area in the upper right panel now shows the value of all the logical tests required for the formula to evaluate to “Lower right quadrant”: these are that in_range is true, left_half is false, and lower_half is true.

FormulaForge Full Window

Some formulas contain nested IF structures that are much more complicated than this example. FormulaForge provides a powerful tool that helps Excel users construct, understand, and debug these formulas.