All right, if you're using Google Sheets,
sometimes you can get a reference error.
So I'm showing one here in cell C15
we're going to talk about how that happened
and how to fix it.
(upbeat music)
So first let's take one step backwards
and just left click on the Undo and start here.
So how does the pound REF or the reference error happen?
The most common way,
there are several ways and we'll get to them,
but the most common way,
if you just delete a row or column where a cell was.
So let's take this example table,
the values in these columns are being added together,
we're using two different techniques here.
So in B16, we're using the sum function
and we're specifying the cells in a range.
So we're using B7:B15.
But if we go into cell C16,
we're using the Plus operator to add them all together,
it's doing the exact same thing,
but it's using a different technique
and you'll see that these are treated differently.
So I'll hit the Escape key
and we'll just come in here
and we will randomly pick a row
going to right click on the row number
and go up to Delete Row.
And that produces the reference error.
So what is happening here?
One is that not all formulas
are going to give you the reference here.
So if you're referencing the cells in a range,
like we had talked about here,
the range is smart enough where it compresses down
after you delete the row
and it figures it out without giving you an error.
But if you were specifically referencing that cell,
which was right here, even inside the formula,
it gives you a reference error and if I hit Escape
the result of the formula is a reference error.
As you can see in this example,
the easiest way to fix this,
is just to use the sum function
with the range or depending on what you're doing,
the average function or count another way to work around it.
So let's go back,
let's put that row back in.
If you don't want this data,
you can just click on the Delete key and get rid of it,
but now produce the error.
And then we can have one more consideration here.
So let's produce the error again. We'll do a Delete.
If you want to leave this formula in here, for some reason,
it's a formula that you don't want to change into a range,
but you want just a better error message.
You could just handle the error
so you could use the if error function
and then have the formula that's producing the error
and then comma, this is what you're going to do
if there is an error.
You can just tell the user,
"Please delete cell contents and not the entire row."
Let's say where you're sharing this spreadsheet
with a lot of other people
that might be a more useful message,
but this doesn't fix the error.
It just gives a message when it happens.
So you can see, instead of getting the reference error here,
you're just getting a message that says,
"Please delete cell contents."
So that could tell your user, oh,
so just go back and just hit the Delete key,
but that's how to handle the error, not to get rid of it.
So if you want to get rid of it,
use a range or just delete the contents
not the whole cell.
So if you want access to the spreadsheet
that has all of the errors in it,
and it has added notes to it.
And even the error message, that's not a specific type.
So there's some information about that,
follow the link in the description
or the link that's on your screen right now,
and you can get access to it.
And if you go to File, Make a Copy,
you can copy it into your Google Drive
and reference it whenever you run into one of these errors.
So let's go on to some different examples of how,
when this can happen.
So we'll scroll down a little bit,
the next way in which it can happen is a circular reference.
So let's rebuild this, let's just say we're summing.
So we're being good here, we're using the range, right?
So we're going to eliminate one way in which it can happen.
Let's hit enter and sum these numbers together
and you got a 56.
Well, sometimes when you're moving data around
or changing formulas,
you accidentally overlap the range
with the actual formula itself.
So let's run this all the way down let's say to B37.
So within this range is B36,
which is the function or the formula itself.
And you can't do that because what that's doing,
that's adding 12 plus eight plus 10 plus 26,
plus the function plus nothing, so it doesn't make sense.
And instead of just looping infinitely,
it stops and gives you the error.
The easiest way to get around this error
is just to look at the error message.
So you can see this little red triangle
pops up in the upper right hand corner of the cell,
and it tells you it's a circular dependency.
So once you see that you can just go into the formula
and you see the cell references are orange,
and then the orange frame goes around the cell reference
and you can pretty much easily see that it's too long.
You need to just change it to B35 to end there.
And that gets rid of it,
but if you want that to stay there, for whatever reason,
if you're doing some sort of calculation,
or what you can do is go to File and Spreadsheet settings.
And you go to the calculation tab and Google Sheets
has something called iterative calculation.
And if you turn this on,
you can tell it how many times
to run through this function before it stops.
So you can do this circular calculation
and just say, "Hey, just do it, let's say five times
and then quit."
If I left click on Save Settings and there we go,
it calculated this five times and obviously 280
is not the sum of these,
but you were able to perform the function.
Now, if I go back into this iterative calculation,
this is really designed for something
that after you calculate it, a number of times,
it starts to hone in on the right answers.
So you can make the number of iterations large.
And then once it hits this threshold
of barrelling in on a result, it would stop.
That's not really practical for this example,
but now that you know that's there,
it may be helpful to you.
Let's turn iterative calculation back off
because it's crazy.
And let's go down to the next example.
So I'll make a little bit of room here,
so you don't have to look at that, spoiler alert.
Okay, so another thing that can happen,
which is relatively simple, conceptually,
but it might throw you off a little bit
when you first see it
is that you can't reference a cell
if it's off of the spreadsheet.
So you can scroll to the right on this grid,
but eventually it stops.
On the left, you can see where it stops right here.
So these cells are doing multiplication.
So if I copy and paste these formulas
and when you're using the spreadsheet, they do adjust.
So if I move this one to the left and down and paste it,
what this is doing is multiplication on A56
and B56, so pretty normal there so far.
But if I cut the formula and I paste it in column B,
it gives a reference here because it can find A56,
but there's nothing to the left of it
and it was trying to reference that.
So if I just go into the cell,
the error is going to say references not exist.
And it doesn't exist because it's kind of off
to the left of the spreadsheet.
So in this case, there is nothing to multiply it by.
But if you take off that reference,
then it's just referencing the cell
and you no longer really doing the division.
So really there's no way to fix that
because the one error that it's referencing is gone.
So you could just do some undoes
until you get to the point where that wasn't happening.
All right, so last one that we have is VLookup.
So it'd VLookup as a formula.
I'll double click on it right here
that looks at a value.
So in this case,
I typed in soup and it's looking
for corresponding value to return.
So I designed it where, hey you know the type of food,
what kind of container does it go in?
So this formula is looking up the value in E73,
and it's using this range.
You see the little purple box around it, B73 to C77.
And it's looking in the second column.
All right, so when it finds soup, it'll go one to the right.
So that's the second column.
You got one, two, and it will return cans.
And let me hit the Escape key.
And that's what's happening here,
when it looks up soda, it returns bottle.
But if you use this formula
and you count the number of columns wrong,
and let's say, I put a three in here,
you can already tell this underlying the function in red.
And it's telling me already the error message,
which evaluates your in and out of bounds range,
which is a way of saying,
"Hey, there's not three columns in this range.
There's only two, ao there's no possible way
that three is right."
You press Enter,
you'll get another pound REF or a reference there.
You just go back in this one
and try to get the column index right.
In this case it's two.
All right, next up, we're going to talk
about how to subtract two dates in a spreadsheet
and what can go wrong and how to fix it.
See you in the next video.