1 00:00:00,500 --> 00:00:03,850 Having issues with the sum is more frequent than you think. 2 00:00:03,850 --> 00:00:08,480 Another common sum error is including sub totals and your grand total. 3 00:00:08,480 --> 00:00:12,340 So for working with this data that shows sales for cities and 4 00:00:12,340 --> 00:00:15,225 then sub totals for this four states. 5 00:00:15,225 --> 00:00:17,750 Now we want to see the grand total at the bottom, 6 00:00:17,750 --> 00:00:20,970 it's important to make sure that you are not double counting. 7 00:00:20,970 --> 00:00:27,210 So if you type in =SUM, and then include the entire column, 8 00:00:27,210 --> 00:00:31,510 you are doubling your total, since you are including each City sales and 9 00:00:31,510 --> 00:00:35,340 then also the State totals which include the City sales. 10 00:00:35,340 --> 00:00:37,120 So you have two options. 11 00:00:37,120 --> 00:00:40,850 You can either manually click each cell that you want to include, 12 00:00:40,850 --> 00:00:43,940 where the easier way is take the auto sum. 13 00:00:43,940 --> 00:00:48,478 And if the sub totals are formulas, it knows to just pick the sub totals. 14 00:00:48,478 --> 00:00:52,670 And you'll see the highlighted cells are the one that's picking up for the total. 15 00:00:52,670 --> 00:00:55,585 Then to check, you can highlight the cells and 16 00:00:55,585 --> 00:01:00,394 heck the sum total at the bottom right of your screen to make sure it matches. 17 00:01:01,991 --> 00:01:05,721 Now you can copy the formula over for the remaining months. 18 00:01:09,651 --> 00:01:14,317 In a previous video, we briefly discussed the absolute 19 00:01:14,317 --> 00:01:16,952 reference the F4 key on PCs. >> So 20 00:01:16,952 --> 00:01:20,770 let's look at an example of how that can create errors. 21 00:01:20,770 --> 00:01:23,050 Let's say we want to forecast sales for 22 00:01:23,050 --> 00:01:27,300 the second half of the year using a 3% growth rate each month. 23 00:01:27,300 --> 00:01:31,057 Let's copy the months out through December and then above our data here, 24 00:01:31,057 --> 00:01:32,943 let's add an input for growth rate. 25 00:01:38,493 --> 00:01:42,231 Now if we take the previous month, which is June, and 26 00:01:42,231 --> 00:01:47,645 we multiply by the cell that has the 103% and drag over, we have an issue. 27 00:01:47,645 --> 00:01:52,785 Because each month, the seller you're multiplying by will carry forward as well. 28 00:01:52,785 --> 00:01:57,820 So what we need to do here is click on the formula, and then for the cell reference 29 00:01:57,820 --> 00:02:02,470 that includes the 103%, we need to click in the middle of the column letter and 30 00:02:02,470 --> 00:02:06,970 row number and hit the F4 key and then hit Enter. 31 00:02:06,970 --> 00:02:09,850 This locks it down as as absolute reference and 32 00:02:09,850 --> 00:02:14,730 now you can see when we carry forward the formula, our multiplier stays the same. 33 00:02:14,730 --> 00:02:19,740 Another common error can come from using hard coded values in calculations. 34 00:02:19,740 --> 00:02:25,060 Using this last example, we could hard code the 3% growth rate into the formula. 35 00:02:25,060 --> 00:02:28,230 If you do it correctly, you'll get the same result. 36 00:02:28,230 --> 00:02:32,450 But I'm not a big fan of hard coding numbers into the formula as I think it's 37 00:02:32,450 --> 00:02:36,940 easier to make mistakes that way, and definitely harder to find the error. 38 00:02:36,940 --> 00:02:41,930 Also, if we want to change the growth rate to 5%, it's much easier to 39 00:02:41,930 --> 00:02:46,990 just change one reference cell instead of dealing with all the hard coded numbers. 40 00:02:46,990 --> 00:02:50,170 So I would advise staying away from hard coded numbers and 41 00:02:50,170 --> 00:02:52,340 formulas as much as possible. 42 00:02:52,340 --> 00:02:56,910 And lastly, the circular reference is a common error caused by a formula in 43 00:02:56,910 --> 00:03:02,570 a cell that refers back to it's own cell, either directly or indirectly. 44 00:03:02,570 --> 00:03:06,400 If we're summing a total here and we accidentally include the total and 45 00:03:06,400 --> 00:03:09,750 the formula, we will get a circular reference error. 46 00:03:09,750 --> 00:03:12,290 The best thing to do when getting this error is 47 00:03:12,290 --> 00:03:16,050 undo your last action right away and then try again. 48 00:03:16,050 --> 00:03:20,500 If you wait it can become more difficult to figure out where it is coming from. 49 00:03:20,500 --> 00:03:23,120 Obviously, there are more errors than what we've covered here. 50 00:03:23,120 --> 00:03:26,300 But I wanted to address some of the more common ones. 51 00:03:26,300 --> 00:03:29,660 In the next video, we'll look at a few more ways to prevent errors.