17. AND OR NOT: Startup and investor excel model training

Intro to Excel model training – AND OR NOT

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 AND OR NOT functions?

These are a part of a series of functions which I call fancy functions. They add into other functions to make them more powerful.

Technically they are called ‘logic gates’ (If you want to turn the math nerd dial up, they are part of Boolean calculus).

There are, in fact, seven basic logic gates: AND, OR, XOR, NOT, NAND, NOR, and XNOR. We are only doing to deal with 3 of these as I never use the others. NOR might be a fringe exception, but it’s not worth your time thinking about for now.

They’re really simple if you think about them:

• If you want to things to happen you would write… AND right?
• If you want to be chill and flexible then you would write if OR things happen
• If you’re trippin and nothing should happen then NOT

Before we get into some real detail, here is an example in the wild. This is how I calculate whether there will be share dilution in a convertible debt loan.

What I want to know is if both (AND):

• The dividend (interest payment) is less than the available revenue so investors get paid
• The conversion price is ‘in the money’ (Less than the current price per share)

Then the loan can be converted into shares and so dilute you.

So getting back to things… IF (Donald is an idiot AND he is president, then he’s going to get impeached, or he steps down)

Now how you use these fancy functions is actually something I forget sometimes!  What you need to do is remember that they enable you to write mini formulas for each option. Let just look at an example using AND.

=AND(“Donald”=”Idiot”,”Donald”=”President”)

This just results in TRUE, which we all know, but that doesn’t help.

So, alone that’s not useful. We want to impeach or get him to step down, right? So we use IF.

=IF( AND(“Donald”=”Idiot”,”Donald”=”President”) , “Impeach”  , “Stepdown”)

If both things are true he steps down. Only if BOTH are true does he get impeached?

Using OR this time…

=IF( OR(“Donald”=”Idiot”,”Donald”=”President”) , “Impeach”  , “Stepdown”)

If either of these things is a fact, Donald gets impeached. But…. if it isn’t TRUE (in a logical sense) that the Donald is President, then he wouldn’t get impeached. So you couldn’t use OR.

Now, NOT is a funny one. What it does is take the inverse of an argument. It doesn’t play the same way as AND or OR. If you applied NOT, then if he is an idiot and president the result would be “Impeach” but embedding NOT means TRUE become FALSE so you take the second option, Stepdown. That doesn’t make any sense.

=IF( AND(NOT(“Donald”=”Idiot”),NOT(“Donald”=”President”)) ,”Impeach”  , “Stepdown”)

What would make sense instead is this:

=IF( AND(NOT(“Donald”=”Idiot”),NOT(“Donald”=”President”)) , “Stepdown” , “Impeach”)

You need to change the TRUE and FALSE syntax.

You would never write a NOT formula like that since it’s more complex than not using it. NOT would be used where you write a complex formula and then you just want to take the inverse of it.

AND, OR and NOT are not something I use every day. For one, they make formulas a little complicated sometimes. The more complicated a formula the more you are likely to mess it up and the harder it is to audit. Sometimes it’s better to make a worksheet a little longer to make it easier to understand. I tend to try to build a nice logical flow so I don’t have to use AND and OR that much.

Of course, this is not always possible, so you have to use AND and OR. In my experience, AND and OR are used quite a lot, but I rarely use NOT. I find positive arguments rather than inverse ones come to my mind far easier, so it could be a function of my mental logic habits. It’s useful to be aware of in any case.

How do I do these things you speak of?

You can se these functions to make either very simple or incredibly complicated formulas. I use them much more when I’m making a mega formula, then for short formulas, tbh. If you are doing something basic, it’s less cognitive load to just add more rows or columns.

I think we have already covered how to use these logic gates, but the syntax is as follows:

AND(argument 1, argument 2, 3, 4, 5…)

OR(argument 1, argument 2, 3, 4, 5…)

AND(NOT(argument 1), NOT(argument 2), NOT(3), 4, 5…)

NOT does not need to be embedded with AND or OR. You can sneak it in in other ways, which is faster to write. This inverts the logic of the whole OR statement.

=IF(NOT(OR(A1=B1,A1=C2)),”YES”,”NO”)

This is a ll a bit nerdy. Why not just do the example and practice!

Example

The excel example sheet will help you to learn this with an example.

To get the training model, subscribe to the series below.