Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses 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:
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up