18. ISNUMBER, ISTEXT and ISBLANK: Startup and investor excel model training

Intro to Excel model training- ISNUMBER ISTEXT ISBLANK

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 ISNUMBER, ISTEXT and ISBLANK?

If you want to do fancy databases and complex formulas you need to expand your pool of utilities. Today we are looking at a bucket of detectives. They do inspection and let you know about the cheating spouse. Not really. but they are detectives.

  • Is there text in the cell?
  • Is there a number in the cell?
  • Is the cell blank like your stare?
Now you can also combine these up with operators like AND to do fancy pants stuff, but you’ll almost always use our good friend IF to ask the question.
Our exercise today is a little more advanced and pragmatic to what we have done so far. I’ve basically ripped out a little section of the new enterprise model I am building and getting you to replicate a bit of it. So this class is based on real stuff to build a fundraising model.
You can see my actual model below and the example is a simplifed version.
You can see the ‘Project’ in orange. Since we need to fill in the new countries and gaps, I wanted to create a reference to show you need to fill in the yellow boxes and sense check that the revenue numbers in the rows should show. So we use an ISBLANK function and a few others to make this happen. If my criteria are not met it doesn’t say project, it’s just blank. Tidy!
2017-07-27_18-57-28.gif

How do I do these things you speak of?

Pretty simple. These badboys wrap around a cell you want to interogate like a mini-formula. Let’s just illustrate it as it’s really simple.

How do you ask if B1 is blank? 

=IF(

ISBLANK(B1),

“Blank”,

“Not blank”)

That was easy right? The same thing applies to ISTEXT and ISNUMBER.

=IF(ISTEXT(B1),”Boom”,”Bam”)

=IF(ISNUMBER(B1),”CHA”,”CHING”)

That’s super simple, right? But you can use the behaviour of the functions to do cool stuff. And you’re going to learn that cool stuff in the exercise today!

Example

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

You are on enterprise sales company and you have a list of large clients that you started working within one country and a few that are in your pipeline. Let’s say case 1 is your actual and case 2 and 3 are different scenarios.

You want to plan the potential expansion of these clients so you need to make an expansion schedule. You care that your model looks attractive too 😉

So you want to estimate the number of countries you are going to expand to and the revenue they might create for you in different scenarios. The goal is to calculate the total expansion revenue under each scenario.

By only changing the case between, 1, 2 and 3 we will make that magically happen!

You can see the problem set below and what the outcome looks like in scenario 1 solution.

The ugly green is just so you are totally clear on what you should be working on 😉

isnum problem.png

isnumber solution.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.