Excel Essentials: Rethinking Nested IF Formulas

The nested IF is the formula that everyone learns when they start to use Excel beyond pie charts and auto sums. For many it’s the first step towards automating a spreadsheet. They can be a powerful tool when used properly, but recently I began equating nested IF formulas to the particle board tables I bought for my first apartment: They did the job until I could get something better. Allow me to share some alternative approaches to nested IF formulas that have helped me immensely.

The Ad-Hoc Report                                      

Let’s say you have an exported report like the one below that only provides order numbers and order status codes.

Your manager wants you to amend the report so it’s easier to read, so you create this nested IF statement based on the code chart:

Sure, it works. But what if you want to add or remove one of the order processing steps? You’ll end up editing most, if not all, of your formula arguments. I would recommend using the CHOOSE function in this scenario.

This is clearer and allows for easier editing. The CHOOSE function evaluates a variable as an index position and returns the associated value from the user’s list of arguments. Since the variable being evaluated is already a number between 1 and 5, the CHOOSE function is a natural fit.

The Tiered Commission Structure

I’ve seen more blunders using nested IF formulas in commission and compensation spreadsheets than anywhere else. Below is a simple commission structure in which the sales reps are paid on a sliding scale:

Things seem to get particularly ugly when multiple ranges are involved. Here’s an example of a nested IF statement base on the commission tier chart that I’ve seen novice Excel users implement:

What a mess. Again, this formula is completely functional, but what happens when the commission structure changes next year and you need to adjust the pay ranges or commission percentages? The LOOKUP function is much better suited for dealing with number ranges.

The LOOKUP function yields the same results but is structured more concisely. Not only is editing the formula easier, but a new user can simply see the formula’s operation. The LOOKUP function basically looks for a value in the look up array and returns the associated value from the result array. In this example, the gross sales amount in B4 is between 20,000 and 30,000 so the function returns 7%.

There are more scenarios in which IF formulas can be replaced with more appropriate functions. It’s time to leave the comfort of the nest and expand your spreadsheet vocabulary.