(soft music)
- [Instructor] All right, if you're using Google Sheets,
and let's just say you have a column of numbers
and you want to calculate the average,
we're going to go over how to do that.
But we're also going to talk
about several different situations
where it might not work.
And we'll discuss alternative methods
to get you to your average
so that we can work with any type of data.
Let me show the first example here,
I'll delete it right now and I'll re-type it,
is just simply using the average function.
So we'll just start typing average,
and Google Sheets will start to auto-fill
this list with functions that it's guessing
that you want to use, so the second one is AVERAGE.
We'll left click on that.
And the only input that the AVERAGE function needs
is just the cells that you wanna average.
It's guessing right here D5 to D12,
and that's going to be right.
So we can left click on that
and it's even telling us the answer already.
All right, so that's the mathematical average
of these cells,
but I'm guessing you came to this video
because you're not able to calculate the average.
All right, you're having some issues
and we're going to talk about five different ways
to fix them.
Let's un-hide some of this data here,
re-hide these cells.
Those will come into play later.
And our second example is taking the average of a list
that isn't all number values, okay?
So it's kind of obvious that this is a number
and this one isn't,
but sometimes it's not so clear,
like you may have here 12, okay?
And an easy way to check this
is just to use the ISNUMBER function.
We'll type that in
and we will point it to E5,
and that saying true, that's a number,
but if you drag this down to the bottom of the range
which you're trying to sum,
you'll see that E6 and E10 are not numbers.
All right, so we're going to delete that little test.
And this is kind of an interesting one
because 3.5 could still be the right answer, all right?
So I'm going to un-hide column F,
and I've kinda put a manual formula in here
to show you what the AVERAGE function is doing.
What it's doing is it's taking only the number values
for the numerator, right?
So it's summing everything that's in a rectangle
in a dotted line here.
And then it's dividing them by the count of them.
So it's the summation of the two, the five, et cetera,
divided by one, two, three,
four, five, six.
If that's what you wanted the AVERAGE function to do,
then your answer is right,
you don't have to change anything.
But if you wanted these to be treated like zeros,
especially if we change this back to no stock,
there's a function for this, so we will
un-hide a row here.
And it's a different function called AVERAGEA.
And what AVERAGEA does is that it will add up
all of the numerical values
and just use a zero for the text values.
So the numerator is still the same here,
but the denominator is going to have two more numbers in it.
So the result is going to be lower, which it is.
And it's 2.63.
All right.
So we'll look at what the AVERAGEA function is doing
if we do it manually
is we're still summing just the numbers,
but the denominator is a count
of the number of numerical values
and text values in the range, right?
So it stands to reason that the result would be
a little bit smaller.
All right, next issue that we can deal with is how
the AVERAGE and the AVERAGEA functions for that matter
deal with hidden rows.
So let's say this row with $665 and a count of six
is going to be hidden.
So I pick this because it's
on the outer bounds of this range of data, right?
This 665 is making the average larger.
And so is the six as well,
but if we right click and hide this,
it doesn't change these calculations.
And maybe that's what you want, and that's fine.
If so, obviously you don't need to change anything,
but there is a function available
that can exclude hidden rows.
So we'll left-click over in between the 15 and the 16
to un-hide row 16, where I have a function called SUBTOTAL.
So I could put it in both columns, let's do that.
And you'll see, this is ignoring the hidden row.
So it's substantially lower than these two values
because that hidden row had 600 and something in it.
All right, so we'll double click in here
and it's the SUBTOTAL function.
And then the subtotal function takes a function code.
So there are several different things
the SUBTOTAL function can do and one of them is average.
In the code 101, ignores hidden values,
and we'll take a quick look at that.
We'll left click on learn more.
These are the available function codes.
One is average, but if you add a 10 before it,
it will skip hidden values.
All right.
So we'll escape out of that
and we'll go onto our next example.
And this would be finding the average of
a range of values that have errors in it.
Okay, so if you just use the AVERAGE function,
we'll select just the values there, I'll hit enter,
and it returns an error, because it's saying, look,
I can't calculate the average
because these aren't all numbers.
It doesn't know what you want to do with the error,
so it just says, there's a problem here.
And maybe that's okay.
You need to be careful changing this function
because probably the best course of action
is to fix the errors.
But if you knew the errors are there
and you still want to move on and make the calculation,
I'm going to go up to the toolbar,
left click on undo,
and we'll talk about what's going on here.
All right, so I'll double click in this cell
and what we're doing here is we're using the FILTER function
and the ISNUMBER test.
Okay, this is averaging all the values
that make it through the FILTER function.
So we've set up the filter to look at G5 through G12
and the test for the filter is if it's a number or not.
All right, so $7 and 62 cents
will make it through to the AVERAGE function,
but the pound value here will not.
So won't come into the AVERAGE function.
So it won't return an error.
So that looks a little high to me.
Let's un-hide these rows,
and this is why, 'cause I have a large value in here
and the AVERAGE function doesn't ignore hidden rows.
So if I hold the control key and I left click on
the values that it's averaging,
we'll double check this by going down
in the lower right-hand corner.
And there it is, the average is 921.41.
All right, but if you use the FILTER function
and the ISNUMBER function together,
you can still do it on the entire column.
All right, so we'll go onto our next examples.
In Google Sheets you can use check boxes,
nd what these are, if you look up in the formula box,
is really just a way to represent a true or a false.
If you want to average these,
so if you want to see if more are checked
than are unchecked,
the AVERAGE function
won't return a value because it doesn't evaluate
anything that's not a number, right.
So we need to come down and use the AVERAGEA function.
And like we talked about earlier,
AVERAGEA look at Boolean values
and if it's a true, it will evaluate it as a one.
If it's a false, it'll evaluate it as a zero.
So if we uncheck one of these,
you can see the average gets a little bit lower.
So if you're averaging true and falses,
in this case it's check boxes,
AVERAGEA is definitely the way you wanna do it.
Right, and then we'll scroll down to
a little bit more complicated of an example,
but not too bad.
If you have a list of values,
but that list of value has subtotals in it,
or in this case, kind of a sub averages,
we'll go through what happens
when you use the AVERAGE function,
and then we'll talk about an alternative.
Each one of these smaller groups of data
has its own average being calculated,
and that's working fine
until you get down to averaging the entire list.
And if you specify that whole range,
it's also going to average the other average functions.
All right, so you don't want to do that.
I'll un-hide this column, come back down,
and a way to work around that is to go back
to using the SUBTOTAL function and you have to replace
all of the averages with it.
So we'll do a SUBTOTAL 101
and each one of these cells where we're taking the average.
And then if you again, (soft music)
use the SUBTOTAL function for your average,
it will ignore the results
of all the other subtotal functions.
All right.
And if you want to learn more about SUBTOTAL,
this next video is going to show you
how it makes that using the SUM function easier as well.
I'll see you in the next video, thanks for watching.