Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
WEBVTT
1
00:00:03.350 --> 00:00:06.065
In this last video,
2
00:00:06.065 --> 00:00:11.860
we're going to look at some very specialized logical functions for dealing with errors.
3
00:00:11.860 --> 00:00:15.450
Sometimes, when we get an error in our workbook,
4
00:00:15.450 --> 00:00:18.410
it's because of a calculation is wrong.
5
00:00:18.410 --> 00:00:20.815
In which case, best we fix it.
6
00:00:20.815 --> 00:00:25.840
But sometimes, we get an error because the inputs are wrong or missing.
7
00:00:25.840 --> 00:00:31.860
And then, to see a nasty error coming back at us, can be disconcerting.
8
00:00:31.860 --> 00:00:35.830
And for us confident users, quite off-putting.
9
00:00:35.830 --> 00:00:37.770
We have an example right here,
10
00:00:37.770 --> 00:00:39.875
where for average new balance,
11
00:00:39.875 --> 00:00:43.570
we're getting this nasty error coming back, #DIV/0!.
12
00:00:43.570 --> 00:00:46.120
This is something you may have seen before.
13
00:00:46.120 --> 00:00:49.234
It basically means you're trying to divide by zero
14
00:00:49.234 --> 00:00:52.320
when you use the average function in Excel.
15
00:00:52.320 --> 00:00:53.625
If the cell is empty,
16
00:00:53.625 --> 00:00:56.215
it doesn't count it as an input.
17
00:00:56.215 --> 00:00:58.440
So what has happened here is,
18
00:00:58.440 --> 00:01:03.495
Excel is trying to get an average of a whole bunch of empty cells and it says,
19
00:01:03.495 --> 00:01:08.570
zero cells divided by zero, well, that's #DIV/0!.
20
00:01:08.570 --> 00:01:12.870
Now, we have not got around to calculating the new balance yet,
21
00:01:12.870 --> 00:01:16.560
and it's quite common to have spreadsheets where you're waiting for
22
00:01:16.560 --> 00:01:22.140
user input and you won't be able to get a decent calculation until that happens.
23
00:01:22.140 --> 00:01:24.210
What we'd like to do is say,
24
00:01:24.210 --> 00:01:25.905
if you get an error,
25
00:01:25.905 --> 00:01:28.470
rather than displaying the error message,
26
00:01:28.470 --> 00:01:35.700
do something else, and that is where the IFERROR function will help us.
27
00:01:35.700 --> 00:01:38.010
So we're going to come to this #DIV/0!,
28
00:01:38.010 --> 00:01:40.298
and we're going to double click,
29
00:01:40.298 --> 00:01:44.085
and you can see this is trying to get an average of a column,
30
00:01:44.085 --> 00:01:47.520
which is unfortunately empty at the moment.
31
00:01:47.520 --> 00:01:52.420
So what we're going to do is just before the =AVERAGE,
32
00:01:52.420 --> 00:01:54.423
we're going to type if,
33
00:01:54.423 --> 00:01:58.670
but this time, we're going to select the IFERROR function.
34
00:01:58.670 --> 00:02:00.780
Now, what the IFERROR function
35
00:02:00.780 --> 00:02:05.820
does is the first argument is going to be your calculation.
36
00:02:05.820 --> 00:02:08.810
If that calculation does not return an error,
37
00:02:08.810 --> 00:02:11.855
you get the answer, the calculation returns.
38
00:02:11.855 --> 00:02:18.090
However, I'm going to type a comma at the end to get ready for the second argument.
39
00:02:18.090 --> 00:02:20.175
If it does return an error,
40
00:02:20.175 --> 00:02:24.760
you can specify an alternative value to go into that cell,
41
00:02:24.760 --> 00:02:28.300
and we would actually like to just leave it blank.
42
00:02:28.300 --> 00:02:32.820
So, I'm just going to type my open double quotes,
43
00:02:32.820 --> 00:02:36.150
close double quotes, and close my brackets.
44
00:02:36.150 --> 00:02:39.585
Now, when I press enter, you will see,
45
00:02:39.585 --> 00:02:41.925
instead of displaying the error message,
46
00:02:41.925 --> 00:02:44.310
is just typing a blank.
47
00:02:44.310 --> 00:02:47.000
Now let's go and fix that new balance issue.
48
00:02:47.000 --> 00:02:50.685
So our balance is going to be our invoice amount,
49
00:02:50.685 --> 00:02:52.540
less our paid amount.
50
00:02:52.540 --> 00:02:53.730
Oh, and of course,
51
00:02:53.730 --> 00:02:57.865
we have to take into account our outstanding previous balance,
52
00:02:57.865 --> 00:03:00.730
so let's just quickly pop that in.
53
00:03:00.730 --> 00:03:03.515
So it's going to be equal to our previous balance,
54
00:03:03.515 --> 00:03:05.395
plus our invoice amount,
55
00:03:05.395 --> 00:03:07.390
less our paid amount.
56
00:03:07.390 --> 00:03:10.225
And when you're done, click enter.
57
00:03:10.225 --> 00:03:14.730
And you can now see the Average New Balance comes back as zero.
58
00:03:14.730 --> 00:03:18.445
And if I drag this down just for the first few people,
59
00:03:18.445 --> 00:03:21.815
it is now getting me an average new balance.
60
00:03:21.815 --> 00:03:23.190
So where there is an error,
61
00:03:23.190 --> 00:03:25.510
the IFERROR will make it blank.
62
00:03:25.510 --> 00:03:28.420
Where there isn't, you'll get the answer.
63
00:03:28.420 --> 00:03:32.185
Now, let's continue copying that formula down,
64
00:03:32.185 --> 00:03:35.265
and it's gone blank.
65
00:03:35.265 --> 00:03:37.720
Again, because we have more errors,
66
00:03:37.720 --> 00:03:40.645
but this error is a slightly different one.
67
00:03:40.645 --> 00:03:43.670
The #N/A actually stands for Not Applicable,
68
00:03:43.670 --> 00:03:46.450
and I should quickly explain why we're getting this.
69
00:03:46.450 --> 00:03:47.935
The way this is working,
70
00:03:47.935 --> 00:03:50.320
we are getting an upload file from the bank
71
00:03:50.320 --> 00:03:53.435
saying which payments have come through from our parents.
72
00:03:53.435 --> 00:03:56.910
And as you can see, at the moment, there aren't very many.
73
00:03:56.910 --> 00:04:02.740
We're then using a function call VLOOKUP to look up the paid amount from that sheet.
74
00:04:02.740 --> 00:04:05.370
Now, we haven't looked at VLOOKUP yet,
75
00:04:05.370 --> 00:04:08.620
so don't stress about VLOOKUP for this video.
76
00:04:08.620 --> 00:04:11.290
This is just a little teaser for something you can
77
00:04:11.290 --> 00:04:14.105
look forward to later on in this course.
78
00:04:14.105 --> 00:04:16.940
But the way the VLOOKUP works is,
79
00:04:16.940 --> 00:04:22.220
if it does not find the thing it's looking for, it returns #N/A.
80
00:04:22.220 --> 00:04:25.255
Now, we could use an IFERROR here as well,
81
00:04:25.255 --> 00:04:28.000
but there's actually something more specific,
82
00:04:28.000 --> 00:04:31.089
and you may have seen it when we did this earlier.
83
00:04:31.089 --> 00:04:34.128
Between my equals and my VLOOKUP,
84
00:04:34.128 --> 00:04:37.195
I'm going to start by typing my IF.
85
00:04:37.195 --> 00:04:43.620
But you'll see, there's a very specific if not applicable, IFNA function.
86
00:04:43.620 --> 00:04:48.000
Now, this is a good choice because NA is not really an error.
87
00:04:48.000 --> 00:04:52.730
And we may find that some of these functions actually return a genuine error,
88
00:04:52.730 --> 00:04:55.105
and we want to see that error message.
89
00:04:55.105 --> 00:04:57.220
What we want to do is say,
90
00:04:57.220 --> 00:04:59.920
if you don't find the value you're looking for,
91
00:04:59.920 --> 00:05:01.175
just make it zero.
92
00:05:01.175 --> 00:05:04.045
Because they obviously have not paid yet.
93
00:05:04.045 --> 00:05:07.630
So I'm going to choose the IFNA function.
94
00:05:07.630 --> 00:05:11.905
And then, just like I did with the IFERROR function,
95
00:05:11.905 --> 00:05:14.825
I'm going to click at the end of my calculation,
96
00:05:14.825 --> 00:05:16.765
type the value that I want to see,
97
00:05:16.765 --> 00:05:19.100
if it does not find the value,
98
00:05:19.100 --> 00:05:22.190
close my bracket, press enter.
99
00:05:22.190 --> 00:05:25.180
And now, when I copy that down,
100
00:05:25.180 --> 00:05:29.115
all of my NAs are replaced with zeros.
101
00:05:29.115 --> 00:05:32.005
My new balance is now corrected,
102
00:05:32.005 --> 00:05:35.040
and my total paid has been fixed.
103
00:05:35.040 --> 00:05:37.375
We have not made any errors go away,
104
00:05:37.375 --> 00:05:40.520
because these were not actually any errors.
105
00:05:40.520 --> 00:05:43.370
It was simply that we did not have the data
106
00:05:43.370 --> 00:05:46.780
so that these functions could perform their jobs properly.
107
00:05:46.780 --> 00:05:51.145
By using the IFERROR function and the IFNA function,
108
00:05:51.145 --> 00:05:57.366
we can actually make our workbooks handle these problems that are not in fact errors.
109
00:05:57.366 --> 00:06:00.955
So our workbooks look attractive and easier to work with.
110
00:06:00.955 --> 00:06:05.581
When we start working with the VLOOKUP function in later workbooks,
111
00:06:05.581 --> 00:06:09.105
you'll know exactly how to stop it returning that ugly error.
112
00:06:09.105 --> 00:06:12.055
Now, don't forget to try the practice challenge,
113
00:06:12.055 --> 00:06:16.830
which will help you bring all of these skills together.9056
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.