15. Operators: Bigger, smaller, equal? Startup and investor excel model training

Intro to Excel model training- OPERATORS

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.pitch deck

You can join up to the training here.

What’s the point of the Operators?

What if? Well, you should remember the class we just did on IF, right?

Well in it we said if something is equal to something else, then something should or shouldn’t happen. So in that example, we used an operator already; ” = ” the equals one.

What we did was say if a cell is apple then write ‘Eat it’ otherwise ‘Bin it.’

What if you want to say if Jim is taller than Mary, then give him a basketball?

Well IF ( Jim’s height is greater than Mary’s height, give him a basketball, otherwise audition for the Wizard of Oz)

That would look more like IF(100    >   50,”Give basketball,”Crush dreams”)

Do you see where we added the “>”?

Now the same thing can be done for all sorts! See the list below:

Equal to    =
Greater than    >
Less than    <
Not equal to   <>
Greater than or equal to   >=
Less than or equal to   <=

How do I do these things you speak of?

Now, the classes are getting harder. You’ll have to have passed the previous classes to be able to do pass today’s exercise! it’s going to be fun.

I’ll teach you a couple of examples of the non-obvious use of operators, but first off, let me give you a little more 101 on operators to make sure you get it…

You use operators anytime you ask a question. Equals is an operator.

Type into a cell “=1=1” and see what happens…

It returns “TRUE” right? Weird huh, well you can use this behaviour to do weird things in formulas some times 😉

You can use operators when you are playing with arrays which will remove data from the arrays.

Weird example

So now to a weird example! I use this when doing complicated tax on the amortisation of intangible math. This is from a crazy enterprise SaaS model I’m finishing up atm.

What the formula is does is say if the rows in the column have data, then add up the rows in the other section that have data. So we are adding the time difference on the tax decution if the tax basis has an intangible.

2017-07-24_18-52-49.gif

Complex example

Here is a complex formula to figure out the staffing requirement for technical staff when delivering on an enteprise project. The operators are applied to dates instead of numbers.

We want to know the particular months of development between initial development and two phases of expansion so we know how many staff need to be hired in a forecast.

In it we are using <= (before or on the month) and >= (after or on the month).

The formula is applied to 5 years of data (60 months / 60 columns) and to 100 rows of client projects! So this powerful formula using operators packs a serious amount of processing punch!

2017-07-24_19-02-13.gif

Hopefully you found this fun! Now time to have a play.

Example

The excel example sheet will help you to learn this with an example. This one is harder than usual, so plan out a little time to do it. If you get really stuck, look at the answer then try reconstruct yourself in the problem section.

operators 1.png

operators 2.png

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.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.