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:
=AVERAGE(LARGE(A1:A10,{1,2,3,4,5}))
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:
=AVERAGE(SMALL(A1:A10,{1,2,3,4,5}))
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.
3 Responses to “How-to create an elegant, fun & useful Excel Tracker – Step by Step Tutorial”
Hi Chandoo,
I am responsible for tracking when church reports are submitted on time or not and the variations from the due date for submission.
Here is the Scenario;
The due date for the submission of monthly reports is on the 5th of each month. and I would like to know how many reports have been submitted on time (i.e, those that have been submitted on or before the due date) I would also want to track those reports that have been submitted after the due date has passed.
How can I create such a tracker?
Hi Chandoo,
I am a member of your excel school.
I was trying to create SOP Tracker I follow all your steps but I keep this error below.
The list source must be a delimited list, or a reference to a single row or cell.
I try looking on YouTube for answer but no luck.
can you help on this?
thanks
Carl.
Dear Mr. Chando,
Rakesh, I'm working in a private company in the UAE. Recently, I'm struggling to get more details about the staff sick, annual, unpaid, and leaves. I would like to get a tracker in excel. Could you please help me in this situation?
I also watching your videos in YouTube. i hope you can help me on this situation.