Excel Unplugged

Sum of ten largest values

This will be a short lesson on three great and sadly unknown functions and how to use them as an array function.

The eighth LARGEst value

One of my favorite questions to propose to listeners is “Give me the eighth largest value from an array”. This is one of the simplest ways to put an otherwise very “Excel knowledgeable” person in a pickle. If you say the largest value, everyone will spit out Max, but if you say second largest, suddenly they are calling Houston 🙂

Well this problem is easily solvable if you know one single function…

Excel functions LARGE and SMALL

LARGE is a very straightforward and easy to use function. All it’s supposed to do is to return the nth largest number from an Array.


Excel function LARGE also has an evil twin sister called SMALL and you will never guess what it does :). Ok, maybe you could guess, but I’ll just tell you. It returns the nth smallest value from an Array.


Excel Function ROW

ROW is a very simple function if you use it on a cell. ROW returns the number of the ROW that that cell occupies. So =ROW(A28) would return 28.

One great trick and also one of the greatest uses is to use it blank, so without any arguments. You simply put =ROW() into a cell and it returns the number of a row that that cell occupies.

But you can also use it as an Array formula if you write something like {=ROW(1:3)}. You will get a vector of numbers from 1 to 3, that is {1,2,3}. Don’t be fooled by the {} curly brackets in a {=ROW(1:3)} formula, it’s not something you write with the formula, it’s something Excel Adds to the formula when you press Ctrl+Shift+Enter to enter the formula into the cell. Ctrl+Shift+Enter is the way you enter an Array formula into a Cell.

Well now we have all we need…

Sum of ten largest values

To achieve this, we will use the following formula


You must(!!!) enter this formula with a Ctrl+Shift+Enter combination (because it’s an Array formula) and excel will change it into


The calculation goes as follows.

First the ROW(1:10) is resolved into


Then this 1 to 10 vector is placed in LARGE and you get a vector with the first largest, second largest,…


Now all that remains is for the SUM function to sum up the numbers.

Sum of ten largest values

With the use of functions described above, you can easily extend this to the sum of bottom thirty values or an average of seven largest and so one…

This brings us one step closer to eternal happiness once more!

Comments 10

  1. Hello, I enjoy reading through your article. I like to
    write a little comment to support you.

  2. Brady says:

    Hi there to every single one, it’s really a fastidious for me to pay a visit
    this website, it consists of precious Information.

  3. Launa says:

    Keep on working, great job!

  4. I leave a response when I especially enjoy a article on a site or I have something
    to add to the conversation. It is a result of the fire displayed in the article I looked at.
    And on this post Sum of ten largest values | EXCEL UNPLUGGED.
    I was actually moved enough to leave a thought :
    ) I do have 2 questions for you if it’s okay.

    Is it simply me or does it seem like a few of these remarks look like coming from brain dead folks?
    😛 And, if you are writing on additional sites, I
    would like to follow you. Could you make a list all of all your social
    sites like your twitter feed, Facebook page or linkedin profile?

  5. Thanks for sharing your info. I really appreciate your efforts and I am
    waiting for your next post thanks once again.

  6. Wonderful blog! I found it while browsing on Yahoo News.

    Do you have any tips on how to get listed in Yahoo News?
    I’ve been trying for a while but I never seem to get there!


  7. It’s a pity you don’t have a donate button! I’d
    certainly donate to this outstanding blog! I suppose
    for now i’ll settle for book-marking and adding your RSS feed to
    my Google account. I look forward to new updates and will share this
    website with my Facebook group. Chat soon!

  8. Wonderful blog you have here but I was curious about if you knew of any discussion boards that cover the same topics discussed here?
    I’d really love to be a part of online community where
    I can get opinions from other experienced people that share the same interest.
    If you have any recommendations, please let me know.
    Appreciate it!

  9. Hi there I am so thrilled I found your blog page, I really found you by error,
    while I was researching on Bing for something else, Anyhow I am here
    now and would just like to say thank you for a marvelous post and a all round interesting blog (I also
    love the theme/design), I don’t have time to browse it all at the minute but I have
    bookmarked it and also added in your RSS feeds,
    so when I have time I will be back to read more, Please
    do keep up the awesome jo.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.