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:
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.
- All Excel LOOKUPs Explained - May 26, 2020
- How to: Power Query File From Folder - April 21, 2020
- Oz’s Excel Tip: Keep a Workbook for Random Data in Excel - January 23, 2020
