Average of Top 5 Values [and some homework]
The other day, while doing consulting for one of my customers, I had a strange problem. My customer has data for several KPIs and she wants to display average of top 5 values in the dashboard.
- Now, if she wants average of all values, we can use AVERAGE() formula
- if she wants top 5 values to be highlighted, we can use LARGE() formula and CF.
but average of top 5?
I said what any consultant would say. “It is possible”
After thinking for a while, I found the solution by nesting LARGE() formula with AVERAGE() formula. Like this:
There is no need to press CTRL+SHIFT+Enter after this formula and it works fine.
You can use similar formula to get Average of bottom 5 values like this:
Now, your home work:
Ok, here is an interesting twist to this formula. My formula works fine as long as the list has at least 5 values in it. But, lets say the input range (a1:a10) is dynamic. That means, it can grow or shrink.
Now, how would you modify this formula so that it works even when there are less than 5 values ?
Go, figure that out. When you are done, come back here and post a comment.
More formula awesomeness:
- Check if two dates overlap using formulas
- Quarterly totals from Monthly data – SUMPRODUCT formula
- Creating automatic rolling months in excel
- Get user names from email IDs
- More excel formula tutorials & examples
Do you want to be awesome in Excel?
Here is a smart way to become awesome in Excel. Just signup for my Excel newsletter. Every week you will receive an Excel tip, tutorial, template or example delivered to your inbox. What more, as a joining bonus, I am giving away a 25 page eBook containing 95 Excel tips & tricks. Please sign-up below:
Your email address is safe with us. Our policies
Leave a Reply
|Win a Netbook – 10000 RSS Contest||Convert Text to Sentence Case using Excel Formulas [Quick Tips]|