Google Sheets - The #DIV/0! Error and How to Fix It
Google Sheets - The #DIV/0! Error and How to Fix It
If you get the #DIV/0! 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
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 #div/0 #spreadsheet
Content
1.1 -> All right if you've arrived at this video,
2.85 -> you might be having a
problem with #DIV/0!.
6.66 -> And what we're going to
do we're going to go over
8.98 -> what causes it and how to fix it.
11.867 -> (cheerful music)
15.44 -> So I've laid out just
a couple of different
17.68 -> small tables here, and
I use these functions.
20.23 -> They're all doing the same thing,
21.59 -> but I just want to show
you a few different ways
23.75 -> that you can get the #DIV/0!.
26.33 -> So our first table
right here is just doing
29.51 -> simple division using
the slash as an operator
32.35 -> and it's just saying C6 divided by B6.
36.07 -> And when you drag this formula
down, when you get to B7
39.65 -> so we'll just do it again
it's already been done,
41.48 -> but it gives an error in row seven.
45.74 -> And it's giving you that error
46.85 -> because it's trying to divide by zero.
49.15 -> So it's really conceptually
pretty simple error
51.9 -> is doing eight divided by zero,
and you can't divide by zero
54.88 -> so it's just returning an error.
56.95 -> So we'll talk about how to fix this
58.31 -> but before we do the exact
same data is here and here
62.5 -> and I'll just go to view and show formulas
66.54 -> and show you this first
table is just using
68.53 -> what I would call a traditional
way of doing the division
71.27 -> the way you would typically
see it is just with the slash.
74.71 -> But you can also use the divide function.
77.75 -> It's doing the exact same thing,
79.21 -> but depending on how you're
constructing your formula,
81.67 -> it may make sense to use
divide and also quotient.
84.9 -> And quotient just doesn't
give you the remainder
86.78 -> so that does whole numbers.
88.38 -> So let's go back to
just showing the results
93.95 -> and we'll talk about how to fix this.
95.28 -> So you're at this video also I'm assuming
97.89 -> because you don't wanna
see divided by zero.
100.43 -> It's ugly and it makes it
look like there's a problem.
102.646 -> So you can develop a
way to handle this error
105.58 -> where it gives you kinda
a more useful message
109.04 -> instead of that error.
110.88 -> So we'll just delete this formula in B7
113.96 -> and we'll make a new one.
114.84 -> What we're going to do,
is we're going to use the
118.046 -> IFERROR function and what you need to do
120.77 -> is give this another function.
122.51 -> It will check to see if it's an error.
124.6 -> And if it is, it'll do one
thing, if not, it'll do another.
127.47 -> So the IFERROR tells you
it's waiting for the value.
130.46 -> So this would be the value
that it checks to see
132.41 -> if there is an error.
133.4 -> So that's going to be C7 divided by B7.
142.3 -> And there you do a comma
and the same value IFERROR.
145.08 -> So there's something wrong with it.
146.62 -> Let's just say, please
enter a value in column B.
153.02 -> So instead of just saying,
there's something wrong,
155.44 -> this message is telling the
user what to do to fix it.
159.79 -> And then close it off
because if there is no error,
161.95 -> We'll just go ahead and run the function.
163.49 -> So when I hit enter, there we go,
165.76 -> please enter a value in column B.
168.4 -> Yes, we need to fit this column
169.79 -> a little bit better there we go.
170.68 -> So we'll copy this formula
172.27 -> we'll put it in D6 where I know it worked,
175.84 -> so it won't put the message
in there and how to fix it.
178.35 -> And we'll copy it in eight, nine too.
180.89 -> That same technique would
work with dividing quotient.
183.09 -> So there's no need to show
you the same thing again.
185.24 -> And we'll just move on to a
bit more of a complex formula
187.667 -> and the point of this one, is to show you
190.6 -> how to find where the zero is coming from.
192.56 -> So if I double click in this,
194.22 -> I just made it a little
bit more is going on here,
196.61 -> but I constructed it,
197.63 -> so it would still try to divide by zero.
199.97 -> And if you come in here and say,
201.35 -> where's this zero coming from?
203.21 -> I know all of these have values.
204.94 -> So what you can do, you can
hold your left mouse button
207.86 -> and select certain parts of the formula
210.55 -> and then the little blue
text that pops up right above
212.99 -> it is going to tell
you what the result is.
214.69 -> So this isn't your zero
this is coming up is one.
218.13 -> Let's highlight this part
and there's where your zero.
221.71 -> So we take zero times one, it's
still zero as shown by that.
227.27 -> And so what you need to do is address
229.6 -> this part of your formula
that's coming up with a zero
233.3 -> and then you'll be fine.
234.34 -> And now let's talk about real simple way
235.99 -> to get a little calendar to pop up,
237.64 -> when you double click into cell.
239.05 -> See you in the next video.
Source: https://www.youtube.com/watch?v=01QNlxnZ9d4