In this tutorial we are going to look at two ways that you can calculate the average sales of accounts, but eliminating the top 5 and bottom 5 accounts which may distort the overall average.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
We have a list of 500 accounts and their sales, and our goal is to calculate the average sales of those accounts, but we want to eliminate the top 5 and bottom 5 so the outliers won’t distort the general average. Some basic data to start with would be:
The next two items I want to calculate are the sum of the top 5 accounts and the sum of the bottom 5. We’ll do this using the SUMIFS function along with the LARGE and SMALL functions. The formulas will be:
You can learn more about the SUMIFS function here.
For both these formulas, the sum range is column B, and the criteria range is also column B. The criteria for that range is all the numbers that are greater than or equal to the 5th largest (or less than or equal to the 5th smallest) number. That will give us the sum of each of those groups. To structure the criteria, I’ve concatenated either “>=” or “< =” (note these operations must be in double quotes) and the LARGE or SMALL functions:
Now we can add those together, subtract them from the overall total and divide by 490 (500 minus those 10) to get the average sales without the 5 largest and 5 smallest accounts.
To do this in one formula would look like this:
Now, there is an easier way to do this in Excel, and that is to use the TRIMMEAN function. The TRIMMEAN function returns the mean of the interior portion of a set of values. The syntax for TRIMMEAN is:
The “percent” is the amount that you want to eliminate from your calculation. Excel will take that percentage and equally divide it and eliminate that amount from the top and bottom of the array. So in our case, we want to eliminate 1% from the top (5/500) and 1% from the bottom, so our formula will be:
And you can see we get the same result in all three versions of our calculation:
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!