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!
Related Posts
- January 26, 2016
The title of this post is actually a question I got on one of the ...
- November 11, 2014
As promised in the Pragmatism vs. Purity in Excel article from ...
- July 29, 2014
A task that comes about every so often in Excel and can be quite ...
Comments 10
Leave a Reply
Excel Unplugged acknowledgements
Donate
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.