SubscribeAverage 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:
=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.
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
| ||
|
Spread some love,
Posts & Navigation
Tags: array formulas, average(), homework, large, Learn Excel, Microsoft Excel Formulas, spreadsheets |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
46 Responses to “Average of Top 5 Values [and some homework]”
Thanks for the tip. It’s always interesting to see a new way of solving a problem. Here’s my shot at your homework.
One way to make this work when the range has less than 5 entries (but at least one) is to dynamically create the second parameter for the LARGE function. Instead of always passing {1,2,3,4,5}, this should only be the case when the range is sufficiently large otherwise a shorter array must be passed. So we can replace this by:
IF(ROWS(A1:A10)>5;{1;2;3;4;5};ROW(A1:A10))
If the range doesn’t happen to start on row 1, the row numbering returned by the ROW function must be corrected. So to be correct wherever the range is, the ROW(A1:A10) should be replaced by ROW(A1:A10) – ROW(A1) + 1.
Finally, this needs be an array function (unlike the original).
The whole formula is the following:
=AVERAGE(LARGE(A1:A10,IF(ROWS(A1:A10)>5;{1;2;3;4;5};ROW(A1:A10) – ROW(A1) + 1)))
Sorry for the follow-up. Just noticed that I missed a few things while translating the formula from German to English Excel. Of course, the separator for parameters is “,” not “;”. The correct formula is:
=AVERAGE(LARGE(A1:A10,IF(ROWS(A1:A10)>5,{1,2,3,4,5},ROW(A1:A10) – ROW(A1) + 1)))
G’day Chandoo,
How about this:
=AVERAGE(IF(RANK(DataRange,DataRange)<=5,DataRange))
where DataRange is your dynamic range.
Of course you can integrate 'averageif' into this if you're using excel 2007 and 2010, but thought this might be friendlier for those using 2003.
Have an awesome weekend!
Hy Brook,
I am using your formula for TOP 5 values AVERAGE but I am not gettion exact answer. As per your formula answer is 6.8 but correct answer would be 10.4.
Can you help how can I will get exact answer.
For example my data as mentioned below:-
Data range is A1:A10
10
12
11
9
4
7
10
3
2
0
If I am useing this formula =AVERAGE(LARGE(A1:A10,{1,2,3,4,5})) than my AVERAGE is 10.4
If I am useing your formula =AVERAGE(IF(RANK(A1:A10,A1:A10)<=5,A1:A10)) than my AVERAGE is 6.8
Why so?
My main reason is using using your formula is I want TOP 50 Values AVERAGE in the range of A1:A95000. How can I will get the correct answer
@Jagdish
You can use either
=IFERROR(AVERAGE(LARGE(A:A,{1,2,3,4,5})),AVERAGE(A:A))or
=IFERROR(AVERAGE(LARGE(A:A,ROW(OFFSET(A1,,,5)))),AVERAGE(A:A))The second formula is an Array formula and must be entered with a Ctrl Shift Enter
In the second formula change the 5 to 50 to get the Average of the Top 50
eg:
=IFERROR(AVERAGE(LARGE(A:A,ROW(OFFSET(A1,,,50)))),AVERAGE(A:A))Ctrl Shift Enter@ Hui,
Thanx for your valueable reply.
This Formula is working fine!!!
Thanx once again.
Assumption:
1. Only numeric data in “Values”
2. Maximum number of data rows data are known
In column “B” lets say, put an if logic which checks
if there is a value in “A1″,
if yes than put the same value,
else, if no value put 0 in column “B1″
[ Formula: =IF(A1="",0,A1) ]
Paste this formula from “B1″ through “B10″ (assuming 10 max rows of data)
Now use SUM formula on new data column “B1:B10″
[ Formula: =SUM(LARGE(B1:B10,{1,2,3,4,5})) ]
Also, use COUNT formula to count number of rows on original data column “A1:A10″
[ Formula: =COUNT(A1:A10) ]
Finally we divide SUM with the COUNT to get AVERAGE on a dynamic list
[ Formula: = SUM(LARGE(B1:B10,{1,2,3,4,5}))/COUNT(A1:A10) ]
Is this too long for a solution.
Looking for more ideas.
BTW … thanks Chandoo, I have been a regular reader and great work.
I have learnt many things from the blog, keep up the good work.
Cheers, Parveen
Create MyDynamicRange equal to
=Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))
Then enter this
=AVERAGE(LARGE(MyDynamicRange,IF(ROWS(MyDynamicRange)>5,{1,2,3,4,5},ROW(MyDynamicRange))))
with Control+Shift+Enter.
How about these:
=AVERAGE(IF(COUNT(A:A)<5,OFFSET(A1,0,0,COUNTA(A:A),1),LARGE(OFFSET(A1,0,0,COUNTA(A:A),1),{1,2,3,4,5})))
or with a named range where "myRange" = "=OFFSET($A$1,0,0,COUNTA($A:$A),1)"
=AVERAGE(IF(COUNT(A:A)<5,myRange,LARGE(myRange,{1,2,3,4,5})))
Hello Chandoo…
Here’s my shortest guess :
= AVERAGE( LARGE( A1:A10 , OFFSET( E3:E7, 0,0, MIN(5, COUNTA( A1:A10 ) ), 1 ) ) )
and press CTRL+SHIFT+ENTER
the helper column E3:E7 contains 1, 2, 3, 4 ,5.
By the way I’ve found a website translating every Excel function into several languages
http://dolf.trieschnigg.nl/excel/excel.html
If anyone figure how to enter a {1,2,3,4,5} array into offset instead of using the helper column…
Cyril
Chandoo,
Here’s how I would do it.
.
=AVERAGE(LARGE(dr,ROW(OFFSET(dr,,,MIN(5,ROWS(dr))))))
.
with Control+Shift+Enter, and “dr” is the dynamic range.
No IF() functions needed, which Dick may be interested in knowing
Regards,
Daniel Ferry
excelhero.com
I used the following formula and it worked fine
=IF(COUNT(B2:B14)<5,AVERAGE(B2:B14),AVERAGE(LARGE(B2:B14,{1,2,3,4,5})))
Some interesting answers in the comments above. A number of formulas are being used that I have never used before….very interesting. Could someone explain the meaning of the use of {} in the LARGE formula. This is something new to me. Thanks.
I would use {=AVERAGE(IF(LEN(A1:A10)>0,LARGE(A1:A10,{1,2,3,4,5})))} (array entered!)
Have a nice weekend!
CC
@Abbas Sura –
{} is how you specify an array of constants. Some of Excel’s functions, such as LARGE, SMALL, AND, and OR (and some others), can work with an array of values without requiring the entire formula to be Array-Entered.
.
In Chandoo’s LARGE formula for the blog entry, the {1,2,3,4,5} array compels the formula to pull out the first largest value in the range, then the second, then the third, then the fourth, and then the fifth, and to average them together for the result. The problem is that if you give the LARGE function this directive and there is no fifth largest value (because there is only four values in your range), the LARGE function will produce and error.
.
Using arrays in formulas can be very potent, as is taking it one step further and making the entire formula an “Array Formula”, as many of the examples above demonstrate, including mine.
.
@CC –
Your formula does not work for me.
.
Regards,
Daniel Ferry
excelhero.com
What about:
=IFERROR(AVERAGE(LARGE(A:A,{1,2,3,4,5})),AVERAGE(A:A))
A non-volatile, non-array (in that it doesn’t require CSE) dynamic range option:
=SUMPRODUCT(LARGE(A1:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)),ROW(A1:INDEX(A:A,(MIN(5,MATCH(9.99999999999999E+307,A:A)))))))/MIN(5,MATCH(9.99999999999999E+307,A:A))
Can be consolidated further by naming last row & dynamic range
lr=MATCH(9.99999999999999E+307,$A:$A)
dr =$A$1:INDEX($A:$A,lr)
To:
=SUMPRODUCT(LARGE(dr,ROW(A1:INDEX(A:A,(MIN(5,lr))))))/MIN(5,lr)
I created a named range “Data” and used offset (=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$200),1)). I then added the name “Data” into the original formula in place of the array (=AVERAGE(LARGE(Data,{1,2,3,4,5}))).
I created a named range “Data” and used “=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$200),1)” in the refers to field. I then replaced the array in the original formulas with “Data”. “=AVERAGE(SMALL(Data,{1,2,3,4,5}))”
@Shel Price –
Your formula will not work when there is less than five entries in the dynamic range. Chandoo’s objective was to find a formula that would work for five or more (like his does and like yours does), but ALSO for less than five entries.
Regards,
Daniel Ferry
excelhero.com
What about:
=IFERROR(AVERAGE(LARGE(A:A,{1,2,3,4,5})),AVERAGE(A:A))
That provides for less than 5, and the top 5 no matter where they are in the column.
Yeah, I saw that after I posted. Tested for more rather than less.
My dodgy tuppence worth (CSE):
IFERROR(AVERAGE(LARGE($A:$A,MID(ROW(INDIRECT(“1:”&MIN(COUNT($A:$A),5))),1,1))),”")
(Error in case there are no values).
MyRange equal to.
=$A$1:INDEX($A:$A,MATCH(9.99E+307,$A:$A))
Formula to get the average
=CHOOSE((COUNT(MyRange)>5)+1,AVERAGE(MyRange),AVERAGE(LARGE(MyRange,{1,2,3,4,5})))
Regards
Dear Chandoo,
With all due apologies to my Hero Daniel Ferry here is a solution from a lesser mortal
With a dynamic range named Data
=IF(COUNT(Data)<5,SUM(Data)/COUNT(Data),AVERAGE(LARGE(Data,{1,2,3,4,5})))
Daniel Ferry, you are not only a hero but an Excel Picasso as well, I really enjoy your Excel Hero blog. But a question please IF i may.
When and IF ever have you used IF. All the solutions that you propose without IF are truly elegant.
Chandoo Thanks for a most inspiring web site
Cheer
Kanti
@Kanti –
Here is a quote from a comment I left one of my readers recently regarding the IF() function:
.
“I am not philosophically opposed to the IF() function. It’s just that I feel it is used too much and often when there are better choices. However, when it comes to throwing an #N/A to force data points to hide themselves on a chart, I think that IF() is an excellent choice. For some reason, Excel is able to conditionally (with the IF() function) output an #N/A at lightning speed.”
.
This comment was from my celtic muse blog entry:
http://www.excelhero.com/blog/2010/05/excel-animated-chart-2.html
.
Kanti, I’ve found over the years that if I force myself to look for solutions that do not rely on the IF() function, two things happen:
1.) I discover angles of attack on a problem that would never have occurred to me otherwise and hence interesting solutions that are sometimes elegant and sometimes not.
2.) The exercise makes me a better formula crafter in all situations.
.
Regards,
Daniel Ferry
excelhero.com
Hi guys,
Hope that this solution can help.
The formula I use is just a slight modification of the formula shown in the article.
Let data_range = cell range that contains the numbers
Key in (or copy and paste) the formula below in a cell
=Average(large(data_range,row(indirect(“1:”&min(5,count(data_range))))))
and press control+shift+enter
In this case, if the range contains more than 5 figures, only the average of largest 5 figures will return; if less than 5 figures, the average of those figures will return.
Please let me know how do you think about it.
Thanks and best regards,
Eliel Lew
=IFERROR(AVERAGE(LARGE(–DynamicRange–;{1;2;3;4;5}));AVERAGE(–DynamicRange–))
if the dynamic range is named NamedRange then:
=IFERROR(AVERAGE(LARGE(NamedRange ;{1;2;3;4;5}));AVERAGE(NamedRange))
David Ferry,
OFFSET is a volatile function and is always recalculated at each recalculation. This can slow down large excel sheets considerably. Should volatile functions be avoided??
@Oscar –
While OFFSET is volatile, it is extremely fast, and with it some amazingly creative solutions can be found. I routinely use it on large projects with many thousands of formulas with no problem. The advice to look for nonvolatile solutions is sound, but it should not be absolute. ROW() is also listed as volatile, but it has NEVER adversely affected one of my projects.
With OFFSET and ROW in particular, it is well worth exploring their use. Every circumstance is unique, but I would never shy away from either of these.
By the way, my name is Daniel (but I do have a brother named David – though I don’t think he knows what Excel is
)
Regards,
Daniel Ferry
excelhero.com
Excellent contributions by everyone. Thank you so much for the creativity and ideas you have shown here.
One observation in general.
When the list has less than 5 values, Average of top 5 is nothing but average. So the simplest way to write this formula can be,
=AVERAGE(IF(COUNT(A1:A10)<6,A1:A10,LARGE(A1:A10,{1,2,3,4,5})))
But then again, you have shown so much more variety in your answers. Thank you.
Hi
This is my solution
=IF(COUNTA(A:A)<5,AVERAGE(A:A),AVERAGE(LARGE(OFFSET(A1,0,0,COUNTA(A:A),1),{1,2,3,4,5})))
Thanks for the amazing site.
Row() is not volatile. It is wrongly listed as volatile
@Chandoo –
That may be the simplest, but I think Brook’s formula (comment #3) is the best.
=AVERAGE(IF(RANK(dr,dr)<=5,dr))
where "dr" is the data range.
.
Elegant. I even liked how Brook used the IF() function. Cudos Brook!
.
Regards,
Daniel Ferry
excelhero.com
[...] July 1, 2010 at 5:47 PM | Posted in General | Leave a Comment Chandoo has a quick tip on how to calculate the average of only the top or bottom few values in a [...]
@Brook – comment #3
Asking for your Solution with Excel 2007
Can you explain, how to write the formula AVERAGEIF (using Excel 2007)?
Chandoo, Thanks for your very inspiring excel site.
Thanks and best regards,
aldus
[...] Average of top 5 values [...]
[...] Average of Top 5 Values – Array Formula in Excel [...]
thanks for the tip it made me perfect in our exam
thank you very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!…………….,,,,<<<<<<<<
CHANDOO I TRIED THIS FORMULA BUT AM NOT GETTING THE ANSWER I MENTIONED
=AVERAGE{LARGE{A1:A10,(1,2,3,4,5)}} .AM NOT GETTING THE ANSWER.
B1=AVERAGE(LARGE(adi,{1,2,3,4,5})) will work definately…
adi is the range for whole column.
you can select range by selecting the whole column A1 to A65536
using ctrl up and down and after selection plz mention adi in
top left cell address area.
Its working completely fine for me
let me know if anyone needs help on this
I hope chandoo will be proud of me..
Hi – being a bit lazy by not defining a dynamic range name – but this formula does the job (i think) =AVERAGE(LARGE(A:A,ROW(INDIRECT(“1:”&IF(COUNT(A:A)
Dear All,
This is the one valued Trick on excel.
I have a question where I want calculate AVERAGE with with TOP 50 values where my data range is A1:A15000.
How can I will calculate I don’t want to type mannually 1 to 50. Inside of LARGE Function values. Is there any way how can I will caluculate?
=IFERROR(AVERAGE(LARGE(A:A,{1,2,3,4,5})),AVERAGE(A:A))
Thanx in Advanced!!!
@Jagdish
Try the following Array formula
=IFERROR(AVERAGE(LARGE(A:A,ROW(OFFSET(A1,,,50)))),AVERAGE(A:A))It has to be entered with Ctrl Shift Enter
Change the Value 5 to 50 to suit