All right, if you're using Google sheets
and you're getting a pound value error,
we're going to talk about what it means,
how to work around it,
and then how to deal with the error if you can't fix it.
So if I look at this little table of data
that I made here,
there is a series of numbers, right?
And they're being added together.
But the problem is that there's one value
in one of these cells that's not a number, is text.
So let's double click in this formula.
We're adding these three together.
But once this formula gets to the third value,
it doesn't know what to do.
And instead of ignoring it,
it throws a pound value error.
So an obvious way to deal with this
would just be to go in and delete the text, right?
And the formula works that way.
So there's a few issues with that.
If you had 10,000 lines,
you wouldn't necessarily know where the text was.
So what you could do is you could add an additional column
and then you could use something like the is text function.
So you may have guessed it,
but this function checks to see if it's text.
So let's apply that function to each of these values,
we'll drag it down
and then it returns a true only on the cell
where there's text.
If you wanted to do this to 10,000 lines,
and then you wanted to just select your data and do data.
I created a little filter.
If you uncheck the falses and just clicked, okay,
you'd be left just with the data
that's causing the problem.
So click delete and it goes away.
So that's one way to deal with the problem.
If you're okay with just deleting what's text,
let's undo this formula,
but let's say that that data needs to stay in there
for some reason,
it's just part of your spreadsheet.
And then you want to work around it.
One easy way to work around that
is to just change the type of formula.
So there are some formulas that can handle blank data
in a cell and it won't give an error.
Now let's look at this formula again,
instead of using the plus signs to do addition,
what you could do is use the sum function
and then give it this range,
and you press enter and it gives you the value of 46.
So that text is still in there,
but the sum function is smart enough to ignore it
and keep going.
Maybe you want an error when there's texts in there
because you want to fix it.
But if you don't,
if you want to ignore it,
you can use some of the more advanced functions
instead of using the operators.
So that's some basic ways to deal with texts.
Let's go down to the next example.
So we're getting an error here.
What we're trying to do is calculate the average.
So it's a similar formula
where you're using operators to add it together,
and then you're dividing it,
but you're getting the same error.
But when you look at this data,
you don't any text, right?
Imagine that B17, maybe is where the problem is,
but it doesn't look like anything's in there.
So let's grab this formula from before the is text
and we will put it in these cells also,
and you're having the same problem.
So what can happen here is if I hit the F2 key
to go into this cell,
you'll see that the blinking cursor
is actually a little bit off
from the left hand side of the cell.
So if I hit the escape key
and I go from one to the left and do the same thing,
the cursor's right next to the left.
So it doesn't appear is that there's any texts in the cell,
but there actually is.
There's a space.
So there can be what they would call hidden characters
in cells that you can't see,
but they're still going to act like text.
So there's again the same two possible solutions here.
There's one just to hit your delete key.
If you don't need that data in there,
just do that and move on.
The other one is here,
this case you can use the average function.
You don't have to change your data
and the function smart enough to ignore the text value.
So these two examples are very similar,
but the second one is just not as apparent
as to what's going on.
Okay, and another type of data
that can give you the pound value error are dates
and dates are looked at by a spreadsheet
as a number.
So for example,
let's take these two dates
and change the number format to number.
Every day has a number of value and it's sequential.
So the day after that is one more.
So in this case,
you can see a difference between the two,
that's the number of days that have elapsed
between these two dates.
So let's turn these back into a date format.
And what we're doing with these two dates
is we're using a function called the date diff function.
And this is I think, kind of a nice little function.
All it does is calculate the difference between two dates
but then you get to tell it what type of unit to return.
So in this case,
we're saying how many years have elapsed
between July 22nd 2020 and July 8th 2000.
It rounds it down.
So it's giving you a nice little 20,
but when you drag this formula down,
it gives a pound value error on row 25.
And if you look, you can pretty easily tell what's going on.
The five dates that are valid have shifted
over to the right,
but Google sheets doesn't shift an invalid date
to the right.
So that's showing you that this date needs to be fixed.
Now, at this point, I could just fix it, all right?
And the function works.
But if you have again,
a larger table of data,
you may need some automated ways to go about fixing this.
So there's two that we'll use.
One is this,
this is a little bit more automated, but it's still visual.
You can highlight the data
and go to format, conditional formatting,
go over to format cells if,
drop it down to custom formula,
type your equal sign and just use is date, all right?
So whenever you use conditional formatting,
if you're just looking to format each cell
based on its own value,
you're going to use a cell reference for the cell
in the upper left hand corner of the range.
So in this case,
it's going to be B24.
So the is date function will start with B24.
And you can see it's already working.
I'll click done,
and it's highlighted all of the good dates.
So this date,
that's not being recognized as the data's not highlighted,
you fixed it,
it would turn back to green.
The other way to do this
is just to use actually the same formula,
but just type it into a cell.
And this would be, if you have a large table of data,
we're going to check B24 this way,
close it off,
and it's saying it's true.
So let's drag this formula down
and drag it to the right.
We'll give these headers.
These are both checking the date.
We can't have this table formatting not working, can we?
Fixed formatting, now it looks good.
So if we had a large table of data,
what you would do
is you would use the is date function
and then what you want to do,
you want to sort these amounts of return false to the top.
So we would select the table,
go to data,
just turn off that filter that we had applied previously
and go back to data, create a filter
and then you could just sort these
to float the falses to the top,
or you could actually filter it,
uncheck true and then you're just left
with the date values that are bad
and you go through and fix them.
And then you get rid of the pound value error.
The next step,
we're going to talk about how to sort your data
based on fill color, cortex color.
So I'll see you in the next video.