20. ISERROR: Keep formulas cleanStartup and investor excel model training

Intro to Excel model training- ISERROR

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 ISERROR?

ISERROR tells you if there is an error in a formula.

Alone that’s a really pointless thing to know. But… surprise surprise, when you use our best friend IF… it becomes the basis of something super useful!

After I make a model, I shove this bad boy into tonnes of formulas. That way everything looks clean and tidy. But how and why do you ask?

Well take a look at the Actuals sheet for the P&L in the ecommerce model. See lot’s of beautiful n/a everywhere. Instead of horrible error messages such as #DIV/0!, we get n/a.

Groovy.

iserror example.png

How do I do these things you speak of?

ISERROR works the same way as ROUND, which we just covered. It wraps around a question and then tells you the answer.

So if you want to know if a formula has an error, you stick ISERROR around it.

Say you want to divide costs over reveue to get a % of revenue…

ISERROR(costs/revenue)

Now we need to use IF to make this useful. ISERROR says TRUE if there is an error. So what we do is use ISERROR on the formula and then in the TRUE part we write “n/a”. Then we input the formula again in the FALSE portion, so if the formula is cool as ice, we see the result.

IF(ISERROR(costs/revenue),”n/a”,(costs/revenue))

Cool, d’ya get it?

Now do it!

I mentioned that I do this a shite tonne… and I’m lazy so I need to be efficient. I’m not doing this manually. No sir.

Now do you know that I have two computers? I’m writing this on my Mac right now. My PC is JUST for excel. Yeah. Mac excel is total BS. Further more you can’t have plugins because no one makes them. Plugins are total boss.

So if you only have a Mac buy a PC first. If you have a PC, then head to http://www.asap-utilities.com/ and download it. It’s free. I love it.

Select the cells you want to automatically add “n/a” to like we just did.

What we are doing is called “add a customer error message.” It’s #3 on the list here.

custom error.png

When you click on #3, this dialogue box opens. type in n/a ad hit enter. BOOOM.

custom error 2.png

This is the best thing ever!!!!!

Example

The excel example sheet will help you to learn this with an example, but doing it manually. But get the plugin and use it once you know how.

iserror.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 *