Intro to Excel model training- Nested IF
This is part of a series on Excel model training focused on helping founders and investors to actually enjoy and get proficient in excel. It’s such an important skill to have, and there’s no excuse to not be able to do all the ‘advanced’ stuff, let alone the basics.
This is a pragmatic course where you will only learn the most useful things; those that I actually use regularly. We’ll go through the obvious applications, but also include some hacks I’ve developed over the years.
To get the excel example for this and all the other instalments, subscribe with the pretty box on the right and you’ll get the excel tip of the day sent directly to your inbox. Within a month and ten minutes practice, you’ll be a boss in no time.
You can join up to the training here.
What’s the point of the nested IF?
You should love IF as much as I do by now (cough, nerd!).
How do I do these things you speak of?
What you need to do is figure out the outcomes you want in the nested IF. Here we want to segment data into high, medium and low. So we know we need 2 IF for three outcomes.
Now where do you place the categories in the chain? Sometimes it’s easier to put high first, other times high might need to be the default outcome.
Huh? It’s hard to explain. You have to experience to really understand, but sometimes your formulas don’t logic out, so you need to fiddle.
In a nested IF there is always a category which becomes a default, or you make zero the default. The default means it doesn’t pass any of your considerations.
Middle is the default in the example above.
We say if B8 is bigger than D4 then call it high… if it isn’t…
Then ask if B8 is less than D5. If that is TRUE than call it low. Now, anything that is not defined as high or low is called medium. Everything else. This property is worth remembering. The default may be what you are targeting and the two filters act to remove outliers.
An example could be paying salary. Your defauly is pay, but you say if the startdate is before the current month, dont pay AND IF the enddate is before the current date, don’t pay. So the time someone is employed has a start and end and the before and after parts are filtered out.
It’s taken me years to get really capable with complex IF statements. I can help you to understand what is posstible, but your goal is to internalise how to construct them yourself. That unfortunately only comes with practice.
If you want to get examples of crazy formulas, get one of my models and follow the logic and try replicate it.
So for now, it’s best you just start doing. It’s just so much easier that way.
The excel example sheet will help you to learn this with an example. You’re going to characterise points into categories and issue bonus points in certain scenarious. Will be fun!
To get the training model, subscribe to the series on the right.
Get in the game
Free tools and resources like this shipped to you as they happen.