- Gašper Kamenšek
- June 24, 2014
- 10 Comments

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

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…

LARGE is a very straightforward and easy to use function. All it’s supposed to do is to return the n^{th }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 n^{th} smallest value from an Array.

=SMALL(Array,n)

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…

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!

Sum up the diagonal of your data anywhere on a Sheet

- January 26, 2016

The title of this post is actually a question I got on one of the ...

Last word in a string with an Array formula

- November 11, 2014

As promised in the Pragmatism vs. Purity in Excel article from ...

Convert Text to Numbers in Excel

- July 29, 2014

A task that comes about every so often in Excel and can be quite ...

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

%d bloggers like this:

Hello, I enjoy reading through your article. I like to

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.

Keep on working, great job!

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?

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

@ExcelUnplugged (Twitter)

https://www.linkedin.com/profile/view?id=137438485&trk=nav_responsive_tab_profile (LinkedIn)

https://www.facebook.com/pages/Excel-Unplugged/277271139123802?ref=hl (Facebook)

Thanks for sharing your info. I really appreciate your efforts and I am

waiting for your next post thanks once again.

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

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!

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!

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.