Average of Top 5 Values [and some homework]

Posted on June 4th, 2010 in Featured , Learn Excel - 93 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 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:


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:

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

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


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


        You can use either

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

        • Joseph says:

          Hi Hui,

          I was just looking through your formula and was wondering if you could help explain Row(Offset)?

          I tried the formula and it works but I'm not sure how it works. I also tried to lock A1 --> $A$1 and the answer came out different but have no idea why. Could you help explain a bit please?

  4. Parveen says:

    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


    Then enter this


    with Control+Shift+Enter.

  6. Steve says:

    How about these:


    or with a named range where "myRange" = "=OFFSET($A$1,0,0,COUNTA($A:$A),1)"


  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


    If anyone figure how to enter a {1,2,3,4,5} array into offset instead of using the helper column...


  9. Daniel Ferry says:


    Here's how I would do it.
    with Control+Shift+Enter, and "dr" is the dynamic range.

    No IF() functions needed, which Dick may be interested in knowing 🙂


    Daniel Ferry

  10. Abbas Sura says:

    I used the following formula and it worked fine


    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!

  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.

    Daniel Ferry

  13. Mike says:

    What about:


  14. dscg says:

    A non-volatile, non-array (in that it doesn't require CSE) dynamic range option:


    Can be consolidated further by naming last row & dynamic range

    dr =$A$1:INDEX($A:$A,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.


    Daniel Ferry

  18. Mike says:

    What about:


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

    (Error in case there are no values).

  21. Elias says:

    MyRange equal to.

    Formula to get the average


  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


    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


  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:

    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.
    Daniel Ferry

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


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


    Daniel Ferry

  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,


    But then again, you have shown so much more variety in your answers. Thank you. 🙂

  30. Jie says:


    This is my solution


    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.


    where "dr" is the data range.
    Elegant. I even liked how Brook used the IF() function. Cudos Brook!
    Daniel Ferry

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

    • saleem says:

      Dear Mr Daniel please read my quetion and answerit i am really in a Need and i ind your answe very usefull

      • Hui... says:

        @Tahir or Saleem ?

        Two different names and same question, what goes?

        You need to setup a column of values for each Range
        A2 =1/3600/24
        A3 = 2*A$2
        A4 = (Row()-1)*A$2

        Then use an =Averageifs function to retrieve your average scores

        If you post the question at the Chandoo.org Forums

        Please attach a file and you will get a more targeted response

  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,

  35. jovie says:

    thanks for the tip it made me perfect in our exam

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

  36. archana says:


  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?

    Thanx in Advanced!!!

    • Hui... says:

      Try the following Array formula
      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:

    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?


    • 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

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

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


  46. sanjeev says:

    I simply used


    where arrr= array

  47. Yassin says:

    i want to count the numbers that means i have 40 to 100 numbers i want to count how many numbers are there between 50 to 59 manually i can count 10 or may be 6 numbers so to calculate using excel what shall i do

    • Hui... says:

      Try using the Countifs() function

      It will be something like: =Countifs(A1:A100, " GT 50", A1:A100, " LT 59")
      Change the range and GT LT etc as appropriate
      GT = Greater than
      LT = Less Than

  48. MarkS says:

    So what would this look like if you wanted to calculate the average of the highest 5% (or 10%) of the numbers. I have a data range that has 4000 values and i want the average to the top 200 (or 400) items?

    Any thoughts??

    • Arun says:

      Assuming your values are in column A, the formula can be modified as

      =AVERAGE(LARGE(A:A,ROW(OFFSET(A1,,,CEILING(COUNT(A:A)*5%,1))))) Ctrl Shift Enter

      for average of top 5% Values.

  49. Craig says:

    Greetings Im trying to create a formula that measures the avg high and low of several different arguments for instance

    =AVERAGE(LARGE(E3:E14,E19:E30,E51:E62,E35:E46,E67:E78,E97:E108,E126:E137,E156:E167,E185:E196,E214:E225,E230:E241,E246:E257,{1,2,3})) is there anyway for this to work.

  50. Nadav says:

    Hi Chandoo,

    That was a great tip man, thanks a lot for posting the formula!


  51. Sandeep says:

    Hi Chandoo,Iran hui,

    hi am working one similar kind of data,


    Each line of Transaction has
    Date, Hour, Job processed, Average processing(dynamic), CPU Util

    Average processing value is dynamic that i should calculate when the CPU util is more than or equal to 90% and i should only consider last 4 Job procesed transactions + current tranaction with CPU util greater or equal to 90 %. Please suggest some way to do this in excel.

  52. Tahir says:

    Hi everyone
    I have done an experiment and sensor gives me reading after every 1ms (millisecond). There are thousands of row ( 3 – 993434) I want to do something in excel to reduce the number of rows e:g average) or through some calculation in a new colum so that I will be able to get the values for every 1sec
    Means it will give me 1 value for one second and 60 values for 1mint

    • Hui... says:

      @Tahir or Saleem ?

      Two different names and same question, what goes?

      You need to setup a column of values for each Range
      A2 =1/3600/24
      A3 =(Row()-1)*A$2
      A4 =(Row()-1)*A$2

      Then use an =Averageifs function to retrieve your average scores

      If you post the question at the Chandoo.org Forums

      Please attach a file and you will get a more targeted response

  53. saleem says:

    Hi everyone
    I have done an experiment and sensor gives me reading after every 1ms (millisecond). There are thousands of row ( 3 – 993434) I want to do something in excel to reduce the number of rows e:g average) or through some calculation in a new colum so that I will be able to get the values for every 1sec
    Means it will give me 1 value for one second and 60 values for 1mint

  54. saleem says:

    For example I have a data set in colum A with 1000000 number of rows I am trying to reduce the data set by averaging every 1500 rows in this data frame, How can i do that. I am very new to Excel

    • Hui... says:


      Same Question, now different question, what is going on ?

      You need to setup a column of values for each Range
      A2 =1
      A3 =(Row()-2)*1500
      A4 =(Row()-2)*1500

      Then use an =Average with an offset to retrieve the various ranges defined by the row numbers above

      If you post the question at the Chandoo.org Forums

      Please attach a file and you will get a more targeted response

  55. saleem says:

    Hi Hui thankx, I am sorry for two Name ist my first and last Name i haved tried to post the question and have not found the question and i again post it with my second Name

  56. saleem says:

    hi hui can i have you time on s kype to get the expert advice

    • Hui... says:


      I don't do Excel Consulting

      You have asked this question about 10 times now,
      Every time I have asked you to:

      "If you post the question at the Chandoo.org Forums

      Please attach a file and you will get a more targeted response"

  57. Ufoo says:

    Thanks all for ingenious solutions!

  58. Steven says:

    Can anyone solve this,

    If l am having an average value of 59% for 5 Days and want to bring it down on 50% on 6th Day for which the value should be 5% of 6th day but l have to do this on manual basis but require a formula to do it so e.g

    1-Jul 2-Jul 3-Jul 4-Jul 5-Jul 6-Jul AVG %
    59% 59% 59% 59% 59% 5% 50%

    • Chandoo says:

      Thanks Steven for your comment and welcome to Chandoo.org

      Assuming your original data is in A1:E1 and target average is in A2, write below formula.
      =A2*(count(A1:E1)+1) - sum(A1:E1)

  59. Steven says:

    Thanks Chandoo: Your a Jew bro... it help after understanding.....

  60. Muhammad Raja says:

    Dear all i want to know that the average of top 100 values in colum A starting from A2 to A101 and put the value in B2: after it is finished with avg of top 100 values then it moves automatically to next avg of A102 to A 202 and put the value in B2

    is it possible that i can do it in excel

    • Arun N says:

      Let me rephrase the question

      Average of A2:A101 in B2
      Average of A102:A201 in B3
      ... and so on.
      i.e., average of 100 numbers at a time in consecutive rows.

      The formula in B2 is :


      the same can be copied and pasted in subsequent rows till the point the result is not blank.

  61. Muhammad Raja says:

    I want to attach one file can i do it ?

  62. PINOY-exceler says:


    Note: I defined the range as "values" for dynamicity.

  63. Ufoo says:


Leave a Reply