The AVERAGE functions in Excel are nice set of functions to get under your tool belt if data analysis is at all part of your workflow. Let’s take a look at the data set below and dive in.
We have count of sales by make and by month. In this example, we’re trying to assess the relative performance of Ford vs. Toyota Sales
The AVERAGE Function
This is simply the arithmetic mean of a select data set. The total divided by number of count of numeric values.
28.625 is average number of sales per month, regardless of car make. This doesn’t tell us much, but it is a good starting point.
The AVERAGEIFS Function
This is a similar function, but we’re trying to find the average IF a certain criteria is true. In our case, we want to find the average sales per month for Toyota vs. Ford
This function takes three mandatory arguments.
- Column where the values are you’re trying to average
- Column to search for criteria
- Criteria
In our case, we’re trying to average column D, but only if Column C is equal to Ford or Toyota only.
After applying this formula to both makes, we can already see that Ford is outperforming Toyota by five cars per month.
The AVERAGEA Function
This function is interesting. It’s just like the average function, but you can evaluate the average of boolean TRUE and FALSE values as well (TRUE = 1 and FALSE = 0). So why would we need this? Keep reading!
We haven’t talked about column E yet. In it is a formula that evaluates whether or not the total sales for the month were over 20. It returns TRUE/FALSE based on the result. Since that tells us which months met our goals, we can use AVERAGEA to calculate the “percent to goal” for the year (100% being more than 20 cars sold each month).
As you can see below, 75% of the time we met this goal for Fords, while only 58% did we sell enough Toyotas.
While this did get the job done, the problem with this function is that it is not very dynamic, ie doesn’t have a built in feature to add an if statement (If there was demand for it, I’m sure there would be an AVERAGEAIFS function by now). Because of this, I had to manually select the range E2:E13 in order to calculate the average this way. Not a big deal with this size data set, but wouldn’t scale with larger data sets.
As an alternative, we can use COUNTIFS to calculate the percent goal. Here is underneath the hood of the formulas:
The COUNTIFS formula allows us to hone in to individual makes while selecting the entire column, something AVERAGEA did not.
(Note: We divide by 12 and 24 based on the months in the year. For Ford/Toyota, we’re counting 12 months of data, while for all makes we have 12 months of data but 24 pieces of data we’re working with)
In any case, the result is the same and we’re done!
