Google Sheets - The #VALUE! Error and How to Fix It
Google Sheets - The #VALUE! Error and How to Fix It
If you get the #VALUE! error when you’re using a formula in Google Sheets, this video will help you to understand what it is and how to fix it.
Get access to the Sheet with all errors: https://bit.ly/sheets_errors
🕒 Timestamps
0:00 intro
0:14 Unexpected text values
2:43 Hidden characters in cells
4:17 Invalid dates
Learn more from Prolific Oaktree:
🎓Master the Fundamentals of Google Sheets - http://bit.ly/PO-Basics
🌳 Next Video - Google Sheets | Sort and Filter by Color • Google Sheets - Sort and Filter by Color
🌳 Related Playlist - Google Apps | Full Tutorials - https://bit.ly/google-tutorials
#googlesheets #VALUE #error
Content
0.6 -> All right, if you're using Google sheets
2.16 -> and you're getting a pound value error,
4.55 -> we're going to talk about what it means,
6.81 -> how to work around it,
7.8 -> and then how to deal with the
error if you can't fix it.
14.29 -> So if I look at this little table of data
16.01 -> that I made here,
16.843 -> there is a series of numbers, right?
19.17 -> And they're being added together.
20.87 -> But the problem is that there's one value
23.5 -> in one of these cells that's
not a number, is text.
27.68 -> So let's double click in this formula.
29.57 -> We're adding these three together.
31.38 -> But once this formula
gets to the third value,
34.34 -> it doesn't know what to do.
35.92 -> And instead of ignoring it,
37.27 -> it throws a pound value error.
40.4 -> So an obvious way to deal with this
42.11 -> would just be to go in and
delete the text, right?
45.73 -> And the formula works that way.
48.64 -> So there's a few issues with that.
49.79 -> If you had 10,000 lines,
51.78 -> you wouldn't necessarily
know where the text was.
54.78 -> So what you could do is you
could add an additional column
58.67 -> and then you could use something
like the is text function.
63.55 -> So you may have guessed it,
64.85 -> but this function checks
to see if it's text.
67.4 -> So let's apply that function
to each of these values,
73.53 -> we'll drag it down
76.49 -> and then it returns a
true only on the cell
79.09 -> where there's text.
80.87 -> If you wanted to do this to 10,000 lines,
83.47 -> and then you wanted to just
select your data and do data.
87.6 -> I created a little filter.
90.56 -> If you uncheck the falses
and just clicked, okay,
94.92 -> you'd be left just with the data
96.74 -> that's causing the problem.
99.22 -> So click delete and it goes away.
101.78 -> So that's one way to
deal with the problem.
103.85 -> If you're okay with just
deleting what's text,
107.25 -> let's undo this formula,
109.57 -> but let's say that that
data needs to stay in there
112.33 -> for some reason,
113.17 -> it's just part of your spreadsheet.
115.77 -> And then you want to work around it.
118.15 -> One easy way to work around that
119.67 -> is to just change the type of formula.
121.82 -> So there are some formulas
that can handle blank data
125.5 -> in a cell and it won't give an error.
127.83 -> Now let's look at this formula again,
129.74 -> instead of using the plus
signs to do addition,
132.45 -> what you could do is use the sum function
136.77 -> and then give it this range,
141.02 -> and you press enter and it
gives you the value of 46.
144.4 -> So that text is still in there,
146.55 -> but the sum function is
smart enough to ignore it
148.64 -> and keep going.
149.58 -> Maybe you want an error
when there's texts in there
152.3 -> because you want to fix it.
153.21 -> But if you don't,
154.043 -> if you want to ignore it,
155.44 -> you can use some of the
more advanced functions
157.95 -> instead of using the operators.
159.89 -> So that's some basic
ways to deal with texts.
162.36 -> Let's go down to the next example.
164.93 -> So we're getting an error here.
166.69 -> What we're trying to do
is calculate the average.
169.34 -> So it's a similar formula
171.24 -> where you're using operators
to add it together,
173.68 -> and then you're dividing it,
174.86 -> but you're getting the same error.
177.07 -> But when you look at this data,
178.75 -> you don't any text, right?
180.34 -> Imagine that B17, maybe
is where the problem is,
184.15 -> but it doesn't look like
anything's in there.
187.32 -> So let's grab this formula
from before the is text
191.13 -> and we will put it in these cells also,
197.42 -> and you're having the same problem.
198.78 -> So what can happen here
is if I hit the F2 key
202.74 -> to go into this cell,
204.12 -> you'll see that the blinking cursor
205.57 -> is actually a little bit off
207.43 -> from the left hand side of the cell.
209.75 -> So if I hit the escape key
211.14 -> and I go from one to the
left and do the same thing,
214.63 -> the cursor's right next to the left.
216.86 -> So it doesn't appear is that
there's any texts in the cell,
219.3 -> but there actually is.
220.2 -> There's a space.
221.46 -> So there can be what they
would call hidden characters
224.29 -> in cells that you can't see,
226.2 -> but they're still going to act like text.
228.32 -> So there's again the same
two possible solutions here.
231.73 -> There's one just to hit your delete key.
233.86 -> If you don't need that data in there,
236.53 -> just do that and move on.
238.02 -> The other one is here,
239.64 -> this case you can use
the average function.
242.81 -> You don't have to change your data
245.211 -> and the function smart enough
to ignore the text value.
249.07 -> So these two examples are very similar,
251.93 -> but the second one is just not as apparent
254.01 -> as to what's going on.
256.22 -> Okay, and another type of data
257.65 -> that can give you the
pound value error are dates
261.29 -> and dates are looked at by a spreadsheet
264.08 -> as a number.
264.913 -> So for example,
265.746 -> let's take these two dates
268.22 -> and change the number format to number.
271.76 -> Every day has a number of
value and it's sequential.
274.6 -> So the day after that is one more.
277.33 -> So in this case,
278.51 -> you can see a difference between the two,
280.8 -> that's the number of
days that have elapsed
282.6 -> between these two dates.
283.75 -> So let's turn these
back into a date format.
289.74 -> And what we're doing with these two dates
291.69 -> is we're using a function
called the date diff function.
295.3 -> And this is I think, kind
of a nice little function.
297.57 -> All it does is calculate the
difference between two dates
300.91 -> but then you get to tell it
what type of unit to return.
303.24 -> So in this case,
304.073 -> we're saying how many years have elapsed
306.28 -> between July 22nd 2020 and July 8th 2000.
311.09 -> It rounds it down.
311.97 -> So it's giving you a nice little 20,
314.11 -> but when you drag this formula down,
316.76 -> it gives a pound value error on row 25.
320.73 -> And if you look, you can pretty
easily tell what's going on.
324.57 -> The five dates that are valid have shifted
326.41 -> over to the right,
327.31 -> but Google sheets doesn't
shift an invalid date
329.65 -> to the right.
330.483 -> So that's showing you that
this date needs to be fixed.
333.73 -> Now, at this point, I could
just fix it, all right?
338.15 -> And the function works.
339.22 -> But if you have again,
340.37 -> a larger table of data,
341.61 -> you may need some automated
ways to go about fixing this.
345.14 -> So there's two that we'll use.
346.7 -> One is this,
348.11 -> this is a little bit more
automated, but it's still visual.
350.48 -> You can highlight the data
352.33 -> and go to format, conditional formatting,
356.22 -> go over to format cells if,
357.68 -> drop it down to custom formula,
361.12 -> type your equal sign and
just use is date, all right?
365.92 -> So whenever you use
conditional formatting,
367.82 -> if you're just looking to format each cell
370.61 -> based on its own value,
372.74 -> you're going to use a cell
reference for the cell
375.23 -> in the upper left hand
corner of the range.
377.5 -> So in this case,
378.333 -> it's going to be B24.
380.4 -> So the is date function
will start with B24.
386.6 -> And you can see it's already working.
387.96 -> I'll click done,
389.93 -> and it's highlighted
all of the good dates.
392.3 -> So this date,
393.64 -> that's not being recognized
as the data's not highlighted,
396.058 -> you fixed it,
397 -> it would turn back to green.
399.04 -> The other way to do this
400.14 -> is just to use actually the same formula,
404.29 -> but just type it into a cell.
406.81 -> And this would be, if you
have a large table of data,
408.91 -> we're going to check B24 this way,
412.51 -> close it off,
413.59 -> and it's saying it's true.
414.91 -> So let's drag this formula down
418.16 -> and drag it to the right.
420.7 -> We'll give these headers.
423.5 -> These are both checking the date.
426.38 -> We can't have this table
formatting not working, can we?
430.32 -> Fixed formatting, now it looks good.
432.03 -> So if we had a large table of data,
433.94 -> what you would do
434.773 -> is you would use the is date function
436.96 -> and then what you want to do,
437.93 -> you want to sort these amounts
of return false to the top.
441.35 -> So we would select the table,
443.24 -> go to data,
444.38 -> just turn off that filter
that we had applied previously
447.53 -> and go back to data, create a filter
452.31 -> and then you could just sort these
454.41 -> to float the falses to the top,
456.78 -> or you could actually filter it,
458.64 -> uncheck true and then you're just left
461.63 -> with the date values that are bad
463.28 -> and you go through and fix them.
464.89 -> And then you get rid of
the pound value error.
467.9 -> The next step,
468.733 -> we're going to talk about
how to sort your data
470.94 -> based on fill color, cortex color.
474.6 -> So I'll see you in the next video.
Source: https://www.youtube.com/watch?v=zXEbfvVUa9A