"CSS Layout Basics" was retired on July 12, 2021. You are now viewing the recommended replacement.
Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Well done!
You have completed Intermediate Excel!
You have completed Intermediate Excel!
Preview
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