Original subtitles

In this last video,

we're going to look at some very specialized logical functions for dealing with errors.

Sometimes, when we get an error in our workbook,

it's because of a calculation is wrong.

In which case, best we fix it.

But sometimes, we get an error because the inputs are wrong or missing.

And then, to see a nasty error coming back at us, can be disconcerting.

And for us confident users, quite off-putting.

We have an example right here,

where for average new balance,

we're getting this nasty error coming back, #DIV/0!.

This is something you may have seen before.

It basically means you're trying to divide by zero

when you use the average function in Excel.

If the cell is empty,

it doesn't count it as an input.

So what has happened here is,

Excel is trying to get an average of a whole bunch of empty cells and it says,

zero cells divided by zero, well, that's #DIV/0!.

Now, we have not got around to calculating the new balance yet,

and it's quite common to have spreadsheets where you're waiting for

user input and you won't be able to get a decent calculation until that happens.

What we'd like to do is say,

if you get an error,

rather than displaying the error message,

do something else, and that is where the IFERROR function will help us.

So we're going to come to this #DIV/0!,

and we're going to double click,

and you can see this is trying to get an average of a column,

which is unfortunately empty at the moment.

So what we're going to do is just before the =AVERAGE,

we're going to type if,

but this time, we're going to select the IFERROR function.

Now, what the IFERROR function

does is the first argument is going to be your calculation.

If that calculation does not return an error,

you get the answer, the calculation returns.

However, I'm going to type a comma at the end to get ready for the second argument.

If it does return an error,

you can specify an alternative value to go into that cell,

and we would actually like to just leave it blank.

So, I'm just going to type my open double quotes,

close double quotes, and close my brackets.

Now, when I press enter, you will see,

instead of displaying the error message,

is just typing a blank.

Now let's go and fix that new balance issue.

So our balance is going to be our invoice amount,

less our paid amount.

Oh, and of course,

we have to take into account our outstanding previous balance,

so let's just quickly pop that in.

So it's going to be equal to our previous balance,

plus our invoice amount,

less our paid amount.

And when you're done, click enter.

And you can now see the Average New Balance comes back as zero.

And if I drag this down just for the first few people,

it is now getting me an average new balance.

So where there is an error,

the IFERROR will make it blank.

Where there isn't, you'll get the answer.

Now, let's continue copying that formula down,

and it's gone blank.

Again, because we have more errors,

but this error is a slightly different one.

The #N/A actually stands for Not Applicable,

and I should quickly explain why we're getting this.

The way this is working,

we are getting an upload file from the bank

saying which payments have come through from our parents.

And as you can see, at the moment, there aren't very many.

We're then using a function call VLOOKUP to look up the paid amount from that sheet.

Now, we haven't looked at VLOOKUP yet,

so don't stress about VLOOKUP for this video.

This is just a little teaser for something you can

look forward to later on in this course.

But the way the VLOOKUP works is,

if it does not find the thing it's looking for, it returns #N/A.

Now, we could use an IFERROR here as well,

but there's actually something more specific,

and you may have seen it when we did this earlier.

Between my equals and my VLOOKUP,

I'm going to start by typing my IF.

But you'll see, there's a very specific if not applicable, IFNA function.

Now, this is a good choice because NA is not really an error.

And we may find that some of these functions actually return a genuine error,

and we want to see that error message.

What we want to do is say,

if you don't find the value you're looking for,

just make it zero.

Because they obviously have not paid yet.

So I'm going to choose the IFNA function.

And then, just like I did with the IFERROR function,

I'm going to click at the end of my calculation,

type the value that I want to see,

if it does not find the value,

close my bracket, press enter.

And now, when I copy that down,

all of my NAs are replaced with zeros.

My new balance is now corrected,

and my total paid has been fixed.

We have not made any errors go away,

because these were not actually any errors.

It was simply that we did not have the data

so that these functions could perform their jobs properly.

By using the IFERROR function and the IFNA function,

we can actually make our workbooks handle these problems that are not in fact errors.

So our workbooks look attractive and easier to work with.

When we start working with the VLOOKUP function in later workbooks,

you'll know exactly how to stop it returning that ugly error.

Now, don't forget to try the practice challenge,

which will help you bring all of these skills together.