Common Errors3:30 with Tyler Tallon
Let's look at some common errors so we can do our best to avoid them.
Please note that this is a new spreadsheet, different from the one you used in the previous stage:
Having issues with the sum is more frequent than you think. 0:00 Another common sum error is including sub totals and your grand total. 0:03 So for working with this data that shows sales for cities and 0:08 then sub totals for this four states. 0:12 Now we want to see the grand total at the bottom, 0:15 it's important to make sure that you are not double counting. 0:17 So if you type in =SUM, and then include the entire column, 0:20 you are doubling your total, since you are including each City sales and 0:27 then also the State totals which include the City sales. 0:31 So you have two options. 0:35 You can either manually click each cell that you want to include, 0:37 where the easier way is take the auto sum. 0:40 And if the sub totals are formulas, it knows to just pick the sub totals. 0:43 And you'll see the highlighted cells are the one that's picking up for the total. 0:48 Then to check, you can highlight the cells and 0:52 heck the sum total at the bottom right of your screen to make sure it matches. 0:55 Now you can copy the formula over for the remaining months. 1:01 In a previous video, we briefly discussed the absolute 1:09 reference the F4 key on PCs. >> So 1:14 let's look at an example of how that can create errors. 1:16 Let's say we want to forecast sales for 1:20 the second half of the year using a 3% growth rate each month. 1:23 Let's copy the months out through December and then above our data here, 1:27 let's add an input for growth rate. 1:31 Now if we take the previous month, which is June, and 1:38 we multiply by the cell that has the 103% and drag over, we have an issue. 1:42 Because each month, the seller you're multiplying by will carry forward as well. 1:47 So what we need to do here is click on the formula, and then for the cell reference 1:52 that includes the 103%, we need to click in the middle of the column letter and 1:57 row number and hit the F4 key and then hit Enter. 2:02 This locks it down as as absolute reference and 2:06 now you can see when we carry forward the formula, our multiplier stays the same. 2:09 Another common error can come from using hard coded values in calculations. 2:14 Using this last example, we could hard code the 3% growth rate into the formula. 2:19 If you do it correctly, you'll get the same result. 2:25 But I'm not a big fan of hard coding numbers into the formula as I think it's 2:28 easier to make mistakes that way, and definitely harder to find the error. 2:32 Also, if we want to change the growth rate to 5%, it's much easier to 2:36 just change one reference cell instead of dealing with all the hard coded numbers. 2:41 So I would advise staying away from hard coded numbers and 2:46 formulas as much as possible. 2:50 And lastly, the circular reference is a common error caused by a formula in 2:52 a cell that refers back to it's own cell, either directly or indirectly. 2:56 If we're summing a total here and we accidentally include the total and 3:02 the formula, we will get a circular reference error. 3:06 The best thing to do when getting this error is 3:09 undo your last action right away and then try again. 3:12 If you wait it can become more difficult to figure out where it is coming from. 3:16 Obviously, there are more errors than what we've covered here. 3:20 But I wanted to address some of the more common ones. 3:23 In the next video, we'll look at a few more ways to prevent errors. 3:26
You need to sign up for Treehouse in order to download course files.Sign up