Sign in or start a free trial to avail of this feature.
3. Max, Min and Average Functions
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.
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
(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.