Celebrate 'The VLOOKUP Book' birthday with us. Last day to get 50% discount on the e-book (31 October only).

Click here for details

Average of Top 5 Values [and some homework]

Posted on June 4th, 2010 in Featured , Learn Excel - 59 comments

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.

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}))

Average of Top 5 Values using Excel Formula

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:

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

59 Responses to “Average of Top 5 Values [and some homework]”

  1. Martin says:

    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)))

  2. Martin says:

    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)))

  3. Brook says:

    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! :)

    • Jagdish Negi says:

      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

      • Hui... says:

        @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

        • Jagdish Negi says:

          @ Hui,

          Thanx for your valueable reply.
          This Formula is working fine!!!
          Thanx once again.

        • Arun says:

          Thanks for the solution.

          A small improvement in formula given above for Top 5.

          The following formula will work in all versions of excel.

          =AVERAGE(IF(COUNT(A:A)<=5,A:A,LARGE(A:A,ROW(OFFSET(A1,,,5))))) Ctrl Shift Enter

  4. Parveen says:

    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

  5. 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.

  6. Steve says:

    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})))

  7. Cyril Z. says:

    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.

  8. Cyril Z. says:

    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

  9. Daniel Ferry says:

    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

  10. Abbas Sura says:

    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.

  11. Cecilia says:

    I would use {=AVERAGE(IF(LEN(A1:A10)>0,LARGE(A1:A10,{1,2,3,4,5})))} (array entered!)
    Have a nice weekend!
    CC

  12. Daniel Ferry says:

    @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

  13. Mike says:

    What about:

    =IFERROR(AVERAGE(LARGE(A:A,{1,2,3,4,5})),AVERAGE(A:A))

  14. dscg says:

    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)

  15. Shel Price says:

    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}))).

  16. Shel Price says:

    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}))”

  17. Daniel Ferry says:

    @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

  18. Mike says:

    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.

  19. Shel Price says:

    Yeah, I saw that after I posted. Tested for more rather than less.

  20. Alan says:

    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).

  21. Elias says:

    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

  22. Kanti says:

    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

  23. Daniel Ferry says:

    @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

  24. Eliel Lew says:

    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

  25. jager says:

    =IFERROR(AVERAGE(LARGE(–DynamicRange–;{1;2;3;4;5}));AVERAGE(–DynamicRange–))

  26. jager says:

    if the dynamic range is named NamedRange then:

    =IFERROR(AVERAGE(LARGE(NamedRange ;{1;2;3;4;5}));AVERAGE(NamedRange))

  27. Oscar says:

    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??

  28. Daniel Ferry says:

    @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

  29. Chandoo says:

    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. :)

  30. Jie says:

    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.

  31. sam says:

    Row() is not volatile. It is wrongly listed as volatile

  32. Daniel Ferry says:

    @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

    • Alex M says:

      I agree that this is a great formula, but after testing it, there is one flaw in that if values tie for 5th place or lower, it will skew the average. This, as opposed to the large formula which will explicitly pull back 5 values.

      Great use of a function I’ve never seen though. Really elegant formula.

  33. [...] 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 [...]

  34. aldus says:

    @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

  35. jovie says:

    thanks for the tip it made me perfect in our exam

    thank you very much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!…………….,,,,<<<<<<<<

  36. archana says:

    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.

  37. Adi says:

    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 :)

  38. Adi says:

    I hope chandoo will be proud of me..

  39. Oli says:

    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)

  40. Jagdish Negi says:

    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!!!

    • Hui... says:

      @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

  41. Robert says:

    This is the formula I made up which includes two added components:
    1. average based on a condition
    2. weighted average if over 4 values.  

    =CEILING(IF(COUNTIF($C$10:$C$60,$B66)>4,AVERAGE(SMALL(IF($C$10:$C$60=$B66,$R$10:$R$60),$W$5) * $W$6, SMALL(IF($C$10:$C$60=$B66,$R$10:$R$60), $X$5) * $X$6, SMALL(IF($C$10:$C$60=$B66,$R$10:$R$60), $Y$5) * $Y$6, SMALL(IF($C$10:$C$60=$B66,$R$10:$R$60), $Z$5) * $Z$6) * 4, AVERAGE(IF($C$10:$C$60=$B66, $R$10:$R$60))), 5)

    Now if someone could improve my formula so that the weighted averages worked with 4 or less entries, I would be impressed (and grateful)!

  42. bill says:

    A few questions… what about calculating the standard deviation of the top five… is it just a simple? Also, what happens if there are three items tied for fifth place? One three items tied for second place?

  43. Michael Lev says:

    What if I wanted not the top 5, but rather the top 50% of the data?

  44. Amy Frain says:

    Hi
    I would like to take the top 5 average for a number of scores in specific columns. What would be the formula say if the columns were F8, I8, M8, O8, R8,T8, W8 for example?

    Thanks

    • Hui... says:

      =AVERAGE(LARGE(($F$8,$I$8,$M$8,$O$8,$R$8,$T$8,$W$8),ROW(OFFSET($A$1,,,5,1)))) Ctrl+Shift+Enter

      or
      =AVERAGE(LARGE(F8:W8,ROW(OFFSET($A$1,,,5,1)))) Ctrl+Shift+Enter

      or
      =AVERAGE(LARGE(myRng,ROW(OFFSET($A$1,,,5,1)))) Ctrl+Shift+Enter
      Where myRng is a Named Formula
      myRng =Sheet1!$F$8,Sheet1!$I$8,Sheet1!$M$8,Sheet1!$O$8,Sheet1!$R$8,Sheet1!$T$8,Sheet1!$W$8

  45. Camahof says:

    I am a college student and I wanted to create an excel sheet so I could easily input my quiz grades and figure out my quiz average. My professor drops our lowest 2 quizes out of 7 but before I had all of my quiz grades I still wanted to know what my average was so I used the above formula with a slight modification so that it will average regardless of the number of values i currently have entered and will give me my average of only my top 5 scores. This is my modified formula:

    =IF(COUNTA(B2:B11)>4,AVERAGE(LARGE(B2:B11,{1,2,3,4,5})),AVERAGE(B2:B11))

  46. sanjeev says:

    I simply used

    =AVERAGE(LARGE(arrr,1),LARGE(arrr,2),LARGE(arrr,3),LARGE(arrr,4),LARGE(arrr,5))

    where arrr= array

Leave a Reply