**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:

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