22. Nested IF: Startup and investor excel model training

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!).

So I’m going to let you in on a seceret now. You can use multiple IF in a formula!

Mind blown.

The downside is, it makes stuff sort of complicated. I totally mess up making these ‘mega formulas.’ But there will be many times that you need to have at least 2 IFs in a formula.

So let’s start with the academic part now.

We’re going to think about IF as a decision tree.

With one IF you can get 2 outcomes. We 2 IFs you can have 3.

Take this example. We can get the result for high and low, but what if we want middle?

Here you can see that we use IF twice. The FALSE of the first IF compounds with a second IF.

Yes, you can keep on chucking in IF into this formula to create more outcomes, but the formulas get super tricky. Where the “)” go messes me up all the time. This is bad, but I’ve literally spent a day working on an IF formula before. The calculation for the staff sheet to calculate the payments to staff is one such nightmare I solved. So if you spend a bunch of time figuring something out, yeah, you’re not the only one in formula hell!

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.

Example

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!