Formula Friday #6

Formula Fridays are short reads, spotlighting a single formula that you can start using straight away.

Sorry the formula you have tried can not be recognised. Please delete and try the formula again.

By show of hands. How many of you have wanted to hit (or worse) your laptop or computer because you are presented with any of the following – #NAME?, #DIV/0!,  #REF!, #N/A, #VALUE!, #NUM, #NULL, ####, #SPILL!, #CALC! – and you just can’t get the formula to work. AND. The Excel wizard thing is just no help. No help. AT. ALL.

You are not alone friends. Which is why this week we’re looking at what the error messages actually mean. Because as they say, understanding leads to acceptance – I don’t know if that is what they say, but it sounds good.

Okay, first error message #NAME?

I like this one. The clue is in the name. Oh yeah, set that one up perfectly. This error will appear when Excel doesn’t recognise something. Specifically if something is misspelt, For example VLLOOKUP instead of VLOOKUP or IFERRROR instead of IFERROR. Or a data range has been entered incorrectly for example data range AA:D4, instead of A1:D4.

I’m sure you’ll be very familiar with the next two error messages

#DIV/0! appears when you are trying to divide by zero or something that represents zero – such as a blank cell. This is the error message that hurts your eyes every time you have a formula which looks at anything vs something else. So pretty much every excel doc you have ever created to assess the performance of something. 

#DIV/0 error message example

An easy fix to stop the error displaying it to wrap your formula in an IFERROR. Which also has the added benefit of making your work look nicer.

In my opinion, #REF! is the most disheartening. Especially when you open a document, that you’ve worked so hard on. Just to discover something has gone horribly wrong and none of the formulas work. 

#REF! error message example in Excel

This message will appear when someone really carelessly, I mean accidentally. Deletes or moves something your formula needed to work. And this can be anything from deleting a cell, table or tab within your current document, to changing the location of a secondary file your formula references. Basically any change to the relative values which makes them no longer valid.

The remedy for this is patience. And a lot of it. You will need to maintain a clear head to find the source of the error and correct it.

Next up, two error messages that are so frequent, you probably don’t notice the anymore

If you use a lot of VLOOKUPs (or other lookup formulas) you will probably be familiar with the #N/A error message. It appears when something can not be found. Either something has been misspelt or is actually missing.

#N/A error message example

#VALUE! will display when the value is invalid for the formula. For example, trying to add text and numerical values or not pressing Ctrl + Shift + Enter when using array formulas.

### and #NUM! are directly linked to number inputs

Both ### and #NUM! Will show when a number is too large. ### can typically be resolved by expanding the column width. #NUM! Also shows when the number is too small or when the calculation is impossible.

#NULL, #SPILL! and #CALC! are less frequent errors

Mostly because they are heavily dependent on formulas and inputs that aren’t super common in the day to day for us Merchies and Marketeers.

But, because we are a naturally curious bunch, we may as well discuss what they actually mean. #SPILL! and #CALC! are error messages which you’ll only come across if you have Office 365 (and whatever comes after it, most likely).

#SPILL! relates to formulas that generate a “spill range”. If you are familiar with the UNIQUE formula in Google Sheets you’ll be familiar with the concept of a “spill range”.

Spill range example from google sheets

In this example the UNIQUE formula is entered into cell B2. And the data range to find the unique values is the data range A2:A456. The “spill range” is then everything underneath cell B2, that will need to be populated with the unique values.

In Google, if there is data obstructring the “spill range” it shows #REF! but in Excel that obstruction would show #SPILL!

#CALC! error message shows when there is a calculation error within an array (data range). This a feature of “new Excel”, so isn’t something you’ll come across too often.

And finally #NULL. This shows when the formula is just wrong. A little dramatic but also true. =SUM(A4 D4) will give the result #NULL because it is missing a comma or colon.

Final thoughts

Now you know how to decode error messages there is no stopping you. Mostly the error messages are self-explanitory, but you also need to know what they mean. And now you do. Hopefully.

Until next time

Ax

Finding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.

Have a project in mind?

I can help bring your ideas to life. Let’s talk about what we can build together.

More like this

Finding an exact match for your needs on the internet can, at times, feel like trying to find the missing pair to your odd sock. You know it’s in the drawer somewhere, but you just can’t see it for love nor money. That’s where I can help, think of me as your Excel Agony Aunt, send in your lonely heart letters and I will find your perfect formula partner.

Have a project in mind?

I can help bring your ideas to life. Let’s talk about what we can build together.