1 00:00:01,100 --> 00:00:06,650 Let's take a look at some of the errors you can run into when you write Excel formulas. 2 00:00:06,650 --> 00:00:09,420 One common error is #DIV/0. 3 00:00:09,440 --> 00:00:12,290 This is probably the easiest one to pinpoint. 4 00:00:12,290 --> 00:00:15,230 It happens when you divide a number by zero. 5 00:00:15,230 --> 00:00:18,060 The other one is the #VALUE error 6 00:00:18,230 --> 00:00:21,920 and this is returned when the wrong type of argument is used. 7 00:00:21,920 --> 00:00:29,570 So for example if you try to multiply a value by a cell that has text in it. The #REF! error results when 8 00:00:29,570 --> 00:00:34,940 you move your formula and you haven't correctly specified which cell references should be fixed 9 00:00:35,390 --> 00:00:38,120 and which should move with your formula. 10 00:00:38,150 --> 00:00:40,620 So which should have relative referencing. 11 00:00:40,850 --> 00:00:46,540 Also, it happens if you delete some of the cells that were feeding your formula. 12 00:00:46,550 --> 00:00:53,990 Next is the #NAME? error. And the most common reason for this error is when the formula has been mistyped 13 00:00:54,080 --> 00:00:57,170 and Excel doesn't recognize the function you're referring to. 14 00:00:57,170 --> 00:01:04,430 So if you've mistyped the SUM function and you just put in "SU" instead of "SUM". That's the error 15 00:01:04,430 --> 00:01:08,240 that you're going to run into. The #N/A! error is a frequent error 16 00:01:08,360 --> 00:01:15,900 if you are using lookup formula. Especially the Vlookup formula and when Excel can't do the matching. 17 00:01:16,020 --> 00:01:19,640 And we're going to take a closer look at this later in the section. 18 00:01:19,670 --> 00:01:21,330 Next is the #NULL! error. 19 00:01:21,410 --> 00:01:27,260 This is quite an uncommon error and it occurs if you haven't specified your ranges correctly. 20 00:01:27,290 --> 00:01:34,940 For example, you type "SUM(B4:B6)" but you forget to put the colon or the comma in between 21 00:01:34,940 --> 00:01:41,570 the cell references. The #NUM! error is another uncommon error and it occurs when you supply an invalid 22 00:01:41,600 --> 00:01:46,110 number to a function that doesn't expect that type of number. 23 00:01:46,110 --> 00:01:53,360 For example, if you're using negatives in a function and that function only expects positive numbers. 24 00:01:53,930 --> 00:01:56,300 The #SPILL error is a new error. 25 00:01:56,300 --> 00:02:00,920 It's only in Office 365 and it occurs if you use dynamic arrays. 26 00:02:00,950 --> 00:02:07,120 So if a spilled range is blocked by text or values then you run into this error. 27 00:02:07,220 --> 00:02:14,360 It's quite an easier error to fix because all you have to do is to remove the range that's blocking your formula 28 00:02:14,360 --> 00:02:15,910 results. 29 00:02:15,910 --> 00:02:21,950 The #CALC error is another new error that occurs with dynamic arrays and it's common for the FILTER function 30 00:02:22,010 --> 00:02:23,930 when it can't find a match. 31 00:02:24,920 --> 00:02:29,450 Let's jump to Excel and take a closer look at some of these errors. 32 00:02:29,450 --> 00:02:35,240 Here I have a list of employee names with their yearly salary and their previous year's salary. 33 00:02:35,240 --> 00:02:36,120 For Robert Spear. 34 00:02:36,140 --> 00:02:37,040 I don't have a number. 35 00:02:37,040 --> 00:02:43,310 I've just typed in "to check" because we need to double check his salary and for the previous year's salary 36 00:02:43,310 --> 00:02:47,510 of two people here I have a zero and an empty cell. 37 00:02:48,020 --> 00:02:50,750 Now I need to calculate the percentage change. 38 00:02:50,750 --> 00:02:57,800 What I can do is to do a yearly salary minus previous year's salary divided by previous year's salary. 39 00:02:58,250 --> 00:03:06,290 Or an alternate way of writing this is, yearly salary divided by previous year's salary minus one. 40 00:03:06,290 --> 00:03:10,310 And since this is a percentage, let's just format it as a percentage, send this down. 41 00:03:10,370 --> 00:03:18,200 I get a #DIV/0! error here because previous year's salary was a zero, so I'm dividing this number 42 00:03:18,260 --> 00:03:19,490 with a zero. 43 00:03:19,490 --> 00:03:22,440 I get an error and the same thing here. 44 00:03:22,520 --> 00:03:29,050 Even though the cell is empty, Excel sees an empty cell in the formula as a zero. 45 00:03:29,120 --> 00:03:31,260 Here I have another type of error. 46 00:03:31,340 --> 00:03:36,990 That's because I'm trying to perform a mathematical operation on text. 47 00:03:37,020 --> 00:03:42,010 The first task to do the moment you run into an error is to check your data. 48 00:03:42,050 --> 00:03:45,220 But in this case my data is actually correct. 49 00:03:45,230 --> 00:03:51,650 There's nothing I really want to change here but I don't want to print this as a report because if someone 50 00:03:51,650 --> 00:03:55,430 sees this they're going to think there is a mistake in my formula. 51 00:03:55,430 --> 00:04:01,000 What I would like returned here is just not to see these error values. 52 00:04:01,010 --> 00:04:05,270 I just want to see empty cells wherever I have errors. 53 00:04:05,270 --> 00:04:10,690 For the rest, I want to see numbers. Here you can use the "IFERROR" function. 54 00:04:10,700 --> 00:04:16,190 All you have to do is to wrap up your formula inside the IfError function. 55 00:04:16,190 --> 00:04:21,890 The first argument of IfError is the value. That's actually the calculation that we're trying 56 00:04:21,890 --> 00:04:23,030 to make. 57 00:04:23,030 --> 00:04:30,150 and the second argument is what do we want returned in case the result of this formula is an error. 58 00:04:30,170 --> 00:04:35,460 And it doesn't care what type of error you run into, any error counts here. 59 00:04:35,480 --> 00:04:37,780 So let's say I want to return an empty cell. 60 00:04:37,790 --> 00:04:45,840 All I have to do is put in quotation, quotation, close bracket, press enter, and send this down. 61 00:04:45,910 --> 00:04:52,280 Now this looks a lot better than the other version. But you don't also necessarily need to just put empty 62 00:04:52,280 --> 00:04:52,700 cells. 63 00:04:52,700 --> 00:05:00,290 You can type in some text, you can say NA and send this down. So wherever it comes across an error it 64 00:05:00,290 --> 00:05:07,650 put in "NA". So the IFERROR function is one common way to handle all types of errors. 65 00:05:07,850 --> 00:05:12,160 But be careful on using this because sometimes the errors are legit. 66 00:05:12,160 --> 00:05:18,220 There might be something wrong in your data set that you need to correct. So use it with caution.