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.

=LARGE(Array,n)

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.

=SMALL(Array,n)

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

=SUM(LARGE(Array,ROW(1:10)))

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

{=SUM(LARGE(Array,ROW(1:10)))}

The calculation goes as follows.

First the ROW(1:10) is resolved into

=SUM(LARGE(Array,{1,2,3,4,5,6,7,8,9,10}))

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

=SUM({1000,999,998,997,996,995,994,993,992,991})

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

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!

Gašper Kamenšek

write a little comment to support you.

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

1. Here are some sites where you can find me or Excel Unplugged:

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!

Thanks

7. It’s a pity you don’t have a donate button! I’d
certainly donate to this outstanding blog! I suppose
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