Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.
Start a free Basic trial
to watch this video
Common Errors
3:30 with Tyler TallonLet's look at some common errors so we can do our best to avoid them.
Downloads
Please note that this is a new spreadsheet, different from the one you used in the previous stage:

0:00
Having issues with the sum is more frequent than you think.

0:03
Another common sum error is including sub totals and your grand total.

0:08
So for working with this data that shows sales for cities and

0:12
then sub totals for this four states.

0:15
Now we want to see the grand total at the bottom,

0:17
it's important to make sure that you are not double counting.

0:20
So if you type in =SUM, and then include the entire column,

0:27
you are doubling your total, since you are including each City sales and

0:31
then also the State totals which include the City sales.

0:35
So you have two options.

0:37
You can either manually click each cell that you want to include,

0:40
where the easier way is take the auto sum.

0:43
And if the sub totals are formulas, it knows to just pick the sub totals.

0:48
And you'll see the highlighted cells are the one that's picking up for the total.

0:52
Then to check, you can highlight the cells and

0:55
heck the sum total at the bottom right of your screen to make sure it matches.

1:01
Now you can copy the formula over for the remaining months.

1:09
In a previous video, we briefly discussed the absolute

1:14
reference the F4 key on PCs. >> So

1:16
let's look at an example of how that can create errors.

1:20
Let's say we want to forecast sales for

1:23
the second half of the year using a 3% growth rate each month.

1:27
Let's copy the months out through December and then above our data here,

1:31
let's add an input for growth rate.

1:38
Now if we take the previous month, which is June, and

1:42
we multiply by the cell that has the 103% and drag over, we have an issue.

1:47
Because each month, the seller you're multiplying by will carry forward as well.

1:52
So what we need to do here is click on the formula, and then for the cell reference

1:57
that includes the 103%, we need to click in the middle of the column letter and

2:02
row number and hit the F4 key and then hit Enter.

2:06
This locks it down as as absolute reference and

2:09
now you can see when we carry forward the formula, our multiplier stays the same.

2:14
Another common error can come from using hard coded values in calculations.

2:19
Using this last example, we could hard code the 3% growth rate into the formula.

2:25
If you do it correctly, you'll get the same result.

2:28
But I'm not a big fan of hard coding numbers into the formula as I think it's

2:32
easier to make mistakes that way, and definitely harder to find the error.

2:36
Also, if we want to change the growth rate to 5%, it's much easier to

2:41
just change one reference cell instead of dealing with all the hard coded numbers.

2:46
So I would advise staying away from hard coded numbers and

2:50
formulas as much as possible.

2:52
And lastly, the circular reference is a common error caused by a formula in

2:56
a cell that refers back to it's own cell, either directly or indirectly.

3:02
If we're summing a total here and we accidentally include the total and

3:06
the formula, we will get a circular reference error.

3:09
The best thing to do when getting this error is

3:12
undo your last action right away and then try again.

3:16
If you wait it can become more difficult to figure out where it is coming from.

3:20
Obviously, there are more errors than what we've covered here.

3:23
But I wanted to address some of the more common ones.

3:26
In the next video, we'll look at a few more ways to prevent errors.
You need to sign up for Treehouse in order to download course files.
Sign up