3. Max, Min and Average Functions

 
Subtitles Enabled

Sign up for a free trial to access more free content.

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.

Summary

  1. Calculating Averages (00:11)

    The AVERAGE function calculates the average value of a range. Similar to SUM and COUNT, AVERAGE takes a single argument, which is the range of cells that you want to find the average value from.

    You can also use AVERAGEIF to calculate the average value of those rows in a range that meet a particular criteria. This takes three arguments. First is the range, which is the values to be checked against the criteria. Second is the criteria, which is the condition defining which rows we should find the average of. Third is the average range, which is the range of values we want to find the average of.

    If you want to find the average of the rows in a range that meet multiple criteria, you can use AVERAGEIFS. Here, you specify the range to be averaged, then specify a criteria range and criteria for each condition that must be satisfied.

  2. Averaging boolean values (03:01)

    In Excel, boolean values are cells which can be either true or false. Using the normal AVERAGE function will not work for boolean values. Instead you should use AVERAGEA. This function assigns a value of 1 to true and 0 to false. It then computes the average based on these values.

  3. Max and Min (04:02)

    The MAX function - short for maximum -  finds the largest value in a specified range. The MIN function - short for minimum - finds the smallest value in a range.

  4. Quartiles (04:36)

    Quartiles arrange data from lowest to highest, then split the data into quarters. The QUARTILE.EXC function should be used to find quartiles. This takes two arguments. First, the array of values where we want to find quartiles. Second is the quartile we want to find. The function can find the first, second or third quartile.

  5. Percentiles (05:27)

    Percentiles offer more flexibility than quartiles. The PERCENTILE.EXC function can be used to compute percentiles. This takes two arguments. FIrst is the array where we want to find percentiles. Second is k, which is a number between 0 and 1 that indicates the percentile we want. For example, 0.9 indicates the 90th percentile.

Transcript

(lively music) - [Instructor] In this lesson, we're going to learn how to perform more calculations on our datasets such as average, max/min and percentiles. Let's jump down below the dataset and start off with an easy one which is the average order quantity.

We simply type equals average and then select the range we want to calculate the average for which in this case is order quantity.

We'll close the bracket and press enter. And this gives us an average order quantity of just over 44 units. We can also calculate the average order amount, we'll type equals average again, we'll select the range, this time it's order Dollar Amount, close the bracket, press enter and we can see the average order amount is $34,435.

As with sum and count, average allows you to specify a criterion before your perform the calculation through average if and average ifs. Let's start by calculating the order dollar amount but just for laptops. So, we'll start by typing average if, we'll first select the range and that's going to be the product type.

We'll then select the criteria which is laptop and then we'll select the average range which is going to be order dollar amount.

We'll close the bracket, press enter and we can now see that the average order dollar amount for laptops is $45,000. Now let's take a look at average ifs and I'll try and calculate the order quantity for tablets but just in January. I'll type averageifs.

I'll first select the range which is going to be Order Quantity, I'll next select the criteria range one which is going to be date, I'll then type the criteria which is less than or equal to the 31st of January 2012, I'll then select the next criteria range which is going to be Product Type.

And the last criteria which is Tablet.

Close the bracket and press enter.

And now I can see that the average order quantity of tablets in January is 50.

As you've probably noticed, average ifs and sum ifs formulas can get quite long but Excel tells you along the way what information is needed next, so all you need to understand is terms like the average range and the criteria range and then you'll have no problem using these commands. You might have also noticed that when I typed Average, that an average A option also appears. Average A is used for calculating Boolean values which in Excel are assigned true or false. Let's quickly create a few Boolean values and try and calculate their average with the typical average function, equals average and I'll select the range.

And we can see that we get a divide by zero error. Now I'll try and calculate their average with average A.

Select the range and the answer I get is 0.75 and this is because average A assigns a value of one to true and zero to false. While this is not directly applicable to our dataset, it might be of use in the future, so just bear in mind if you want to calculate the average of Booleans, use average A. Let's now move onto max and min. These are very simple functions and operate the same way as average, so for the Max Order Quantity, I'll type max and then select the range for Order Quantity.

Close the bracket, press enter and we can quickly find the max order quantity of 90. To find the minimum order amount, I'll type min, select the range, close the bracket and press enter to find the minimum order amount of $16,000.

Now we'll take a look at quartiles which are a bit tricker. Quartiles list values in order from lowest to highest and then split this list into quarters. To use this function, we'll type equals quartile and I can actually just select the QUARTILE.EXC option by pressing tab and then we'll select our array.

And then we'll press comma and then we have to type a number. If I type one, it gets me the first quartile, two, the 50th percentile or the median value and three, the third quartile. I'll type the first quartile, so I'll type one, close the bracket and press enter. And this tells me that the bottom quartile of orders are all under $20,000. I'll leave it to you to calculate the median order amount as an exercise.

Quartiles obviously limit you to 25%, 50% and 75% but if you'd like more flexibility, you can use percentiles. For example, if I wanted to know what value the top 10% of my orders start at, I'll want the 90th percentile.

Let's type equals percentile and again I'll select the .EXC by pressing tab, then I'll select the array which is going to be Order Dollar Amount and then I'll type 0.9 to correspond to the 90th percentile.

Close the bracket and press enter.

And this tells me that 90% of my orders are under $56,000 in value but the top 10% of orders are over $56,000. I'll leave it up to you to calculate the 10th percentile as an exercise. The .EXC extension should always be used with quartiles and percentiles. Unfortunately before Excel 2010, this command didn't exist and the old percentile command gave some strange answers. So, if you're on Excel 2007, it's better to manually calculate percentiles, rather than using the built-in function. You might be wondering why I haven't covered max if and min if but these don't actually exist in Excel. They need to be calculated using array functions in a future lesson. In the meantime, make sure that you understand all of the commands we used today because they're some of the most commonly used when manipulating datasets.

Excel Excel for Business Analytics Learning Plan
Formulas and Functions

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial