3. Max, Min and Average Functions

 
Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial

Overview

Basic statistics can quickly yield helpful insights from our datasets. Here I show you how to use Excel's statistics formulas on a company's sales data.

Lesson Notes

MAX, MIN and AVERAGE

=AVERAGE(range): Calculates average value within a range
=AVERAGEIF: Calculates average value for cells that fit a single criterion
=AVERAGEIFS: Calculates average value for cells that fit multiple criteria
=AVERAGEA: Calculates the average for TRUE/FALSE values
=MAX(range): Calculates maximum value within a range
=MIN(range): Calculates minimum value within a range

Transcript

In this lesson, we're going to learn how to perform average maximum and minimum calculations on our sales data.

Let's start off with an easy example, which is finding the Average Order Quantity.

For this we'll use the AVERAGE function, which accepts a range of cells and returns the average value.

So I'll write “=average”, and open a bracket.

And the range of cells will be the order quantity.

I'll then close the bracket and press Enter.

And this tells me that my average order quantity is 44.35 units.

As with COUNT and SUM, AVERAGE has sister functions: AVERAGEIF and AVERAGEIFS, which allow us to apply criteria to an average function.

Say for example, we want to find the average order quantity, but only for January.

We'll use the AVERAGEIF function and open a bracket.

We first need to select the range, which is going to be the order date, and the criteria will be less than or equal to the 31st of January, 2012.

And the average range will be the order quantity column.

I'll then close the bracket and press Enter.

And this tells me that our Average Order Quantity in January was 50 units.

Now let's take a look at the MAX function.

This function simply accepts a range of cells and returns the largest value in that range.

Let's find the maximum order dollar amount by writing “=max” and opening a bracket.

And our input range will be the Order Dollar Amount column.

I can then close the bracket and press Enter.

And this tells me that the Maximum Order Dollar amount for January to March is $72,000.

The MIN function works the exact same way.

To find the Minimum Order Quantity, I'll write “=min” and open a bracket.

I'll then select the order quantity column with Ctrl + Shift + up arrow, close the bracket and press Enter.

And this tells me that my minimum order quantity was 20 units.

Unlike AVERAGE, COUNT and SUM, Excel doesn't contain an in built MAXIF and MINIF function.

However, it is possible to create this functionality using array formulas, which I'll show you in lesson six.