December 2

On using complex IFs and Chapter 3 (now free!)


Hello everyone! I’m releasing chapter 3 from my new book, Advanced Excel Essentials, for free!
Click here to download Chapter 3! Then buy it on Amazon today!
So over at Excel.TV I wrote a blog post that’s an extension of chapter 3 of my book on using IF formulas. Sorry for the dear followers of this blog who aren’t also following Excel.TV. We try to keep up the blog on Excel.TV, which means some content that would do well here gets posted there. And, the SEO wonks tell me that cross posting (that is, copying and pasting the blog post), is bad for business. I dunno. I was never one for SEO.
So I’m going to reproduce most of it here, search engine ranking be damned!

Solutions should naturally extend from their underlying problems

You see, my argument about IFs, and really about all functions and formulas, is that they should be a natural fit to the underlying problem. In particular, nested IFs are often employed as the solution to every complicated scenario. A good example of this is Microsoft’s own help section on using nested IFs, which I’m excerpting below:
2014-11-19 12_07_43-IF - Excel
Without a doubt, the nested IF setup Microsoft presents works as intended. However, we have to think about what nested IFs are saying about the underlying problem. So let’s get a little philosophical about this. Let’s say your raw score for the overall course was a 65–so you should receive a D (next time, turn in your homework). But think about this: was the reason you received a D the direct consequence of not receiving an A, then not receiving a B, then not receiving a C? Not really. Receiving a D is the result of your raw score; it’s not conditionally dependent upon not receiving A, B, C or F. In other words, finding what letter grade you receive is really a lookup problem. It doesn’t require branching conditions.
If that all sounds confusing, think of the problem this way. How would these nested IFs above appear as a decision tree? It would be a bit of a mess, since you would be testing for the numerical region at each step. Again, however, finding where your letter grade falls within numerical bounds is not a decision problem. So I’m advocating we use nested IFs when the underlying problem can be described as a decision problem. In short: if you can draw a nice decision tree out of the problem, chances are nested IFs are a good fit.

Creating Development Memory

The real question however is who cares? If a given solution works, why should it matter? My answer is simple. When we use IFs, we’re often modeling complex problems. If we hand the spreadsheet off to someone else, it will be that much harder for them to understand what’s going on. When IFs are a natural fit however, comprehension is much easier. And this makes sense: since the formulas simply become an extension of the underlying problem. But the real person you should do this for is yourself. Not you, right now; but the the you six months from now, who is going to look back at his or her old workbook. Using the correct formulas to match the underlying problem creates what I call, development memory. And it’s much easier to access previous development memory when the problem and implementation are intrinsically connected.
Underlying all of this is rethinking how we interact with our technology. Some people have pushed back on what I’ve been advocating. Usually the argument is some form of, I use what works for me, and that’s all that matters. But the last few years have presented significant interest in spreadsheet errors and their impact. I’m simply advocating that Excel be more than just a tool for data. If we think of Excel, and related technologies, as extensions of underlying problems, we’ll potentially mitigate the problem in which our model implementations become so complex, they’re hard to work with and understand. However, if we view Excel as an extension of the underlying problem, than our work is only as complicated as the problem itself.



You may also like

December 20, 2013

100+ Best Excel Resources And Websites

August 6, 2019

How to Create a Relative File Path in Power Query

April 9, 2011

How to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell

September 17, 2012

The Excel Rollover Mini FAQ

February 17, 2014

It’s time to say “Goodbye,” to Hungarian Notation
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Free Power User Quick Guide

Discover The BEST Ways To Use Lookups And Conditional Calculations Quickly And Easily With This Reference Guide -- You Won’t Want This To Leave Your Side

With so many ways to use Excel, it can be difficult to memorize all of the key functions, calculations, and techniques you can employ to meet your goal: simply get the job done.