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.
One Response to “How to compare two Excel sheets using VLOOKUP? [FREE Template]”
Maybe I missed it, but this method doesn't include data from James that isn't contained in Sara's data.
I added a new sheet, and named the ranges for Sara and James.
Maybe something like:
B2: =SORT(UNIQUE(VSTACK(SaraCust, JamesCust)))
C2: =XLOOKUP(B2#,SaraCust,SaraPaid,"Missing")
D2: =XLOOKUP(B2#,JamesCust, JamesPaid,"Missing")
E2: =IF(ISERROR(C2#+D2#),"Missing",IF(C2#=D2#,"Yes","No"))
Then we can still do similar conditional formatting. But this will pull in data missing from Sara's sheet as well.