- Need a formula for average in Excel.
Let me show you five different formulas,
how to read them, create them, and when to use them.
(soft music)
Hey, I'm Sharyn from Excel At Work
where we offer straightforward, non-technical assistance
to help you save time, work smarter,
and improve your Microsoft Office skills.
Now, if you're using my "100 Plus Steps to Proficient"
workbook to improve your Excel skills,
I've popped the topic number this video relates to
in the description box.
If you haven't yet claimed a copy of this workbook,
I've also added a link
to where you can grab yourself a copy.
Alright, let's have a look at how we can average in Excel.
So to calculate the average,
what we can do is we can take the sum of the numbers
and divide them by the total numbers of values in a set.
So in this worksheet, we have some sales data.
And for this example,
I'm gonna show you how you can use the SUM function
and the COUNT function to be able to create your average.
So what we have here is the SUM function
and it's looking down our total sales dollars column,
and it's then returning our total.
Now in the next cell down, we have a COUNT,
and if I just show you here, that is looking down the rows.
So you can see here that what it's doing is
it's counting how many rows actually have data in them.
Now just understand that using the COUNT function,
it will only count cells that contain values.
So if your information actually contains maybe text,
then you can use something called the COUNTA function.
So it just looks like this.
And I call it the "COUNT any" as in "anything" function.
So you could use the COUNT or the COUNTA.
Now taking the results from both
of those different formulas,
if we come down to cell B5,
you can see that all we are doing is we're taking the total
and dividing it by the number of values in the set.
So there you go. That's one formula that we can use.
But that's not all that Excel offers.
So let's continue and have a look at other formulas
that we can use within Excel.
Alright. Let's come down to the AVERAGE function worksheet.
Now in here, we have the AVERAGE function
and this is so easy to use because it does everything
that I've just shown you just by itself.
So if I just double-click into the cell,
you can see that we are getting an average
for all of the cells that are within that range.
How easy is that, right?
Additionally, you can do more with AVERAGE within Excel.
So let's have a look at AVERAGEIF.
So I'm just gonna come over to AVERAGEIF,
and you can see on here that we have the opportunity
to be able to be very specific about the data
that we're wanting to average.
Here we're actually asking,
"Can you please just average if the sale was made by Peter?"
So let's have a look.
I'm just going to double click into the formula there,
and you can see with the AVERAGEIF function,
the blue area is what we're actually evaluating.
And Excel will look down that column
and look for the word "Peter"
and the range highlighted in red
is what we're actually averaging.
So what Excel's doing is it's making its way down here
and it'll find Peter,
and then it'll add this information on the same row
into the data that needs to be averaged.
Come down here, do the same here,
and come down here and do the same here.
To return us an average for Peter's sales only.
How great is that, right?
But Excel lets us do an even deeper dive.
So let's go and have a look at AVERAGEIFS.
Now, in here we can be more specific.
So what we're going to do now is
we're going to actually ask,
"Hey, give us the sales for Peter,
but only the sales he made on the 10th of November."
So look at this, I'll just do a double-click up here,
so we can see the formula.
So we've got another layer of information
that we're giving to Excel
so that we can do a deeper dive into this data.
So we're now saying, "Okay, find Peter's information,"
down the red column, and then jump over
and find any information where you found Peter
that is actually for the 10th of November.
And you can see that we've only actually got
those two sales, this one here and this one down here.
And of course that then allows us
to be very specific about what we want to return.
So I'm just gonna press my Enter key,
so you can actually see what was returned.
And of course, now we have that information,
specifically, for those two sets of data.
Now, the AVERAGEIFS function allows you to have more
than two sets of data, thankfully.
So you can actually build multiple criteria
or multiple questions into your average.
And, if at any time, you want to check
that your average is correct, watch this.
You can just go up here.
So let's find Peter's sale on the 10th there.
I'm holding down my Control key,
and I'm just gonna go down to his second sale,
which is down here, and I'm clicking.
Now in doing that, I've just selected those two cells.
You can see that we've got $2,000.43.
And down here on the actual status bar, we can see the same.
So we know we are good. We're good to go.
Now, if this has been helpful,
please let me know in the comments below.
And if you use Excel and want to stay connected,
please consider liking, following, subscribing
because I'm sharing new videos regularly.
And if you know someone who would benefit from this help,
please be sure to share this video with them.
Hey, thanks for watching and have a fabulous day.