0
+-----+-----+-----+-----+-----+-----+-----+-----+
|     | A   | B   | C   | D   | E   | F   | G   |
+-----+-----+-----+-----+-----+-----+-----+-----+
| 1   | 5   | 6   | 5   | 9   | 4   | 8   | 3   |
+-----+-----+-----+-----+-----+-----+-----+-----+

Considering the above sheet. I am looking for a way in Excel/Calc to get the top 5 numbers.

The only way I can think to do it so far is to use 5 different LARGE functions

=LARGE(A1:G1, 1)
=LARGE(A1:G1, 2)
=LARGE(A1:G1, 3)
=LARGE(A1:G1, 4)
=LARGE(A1:G1, 5)

That would give me the five highest numbers. I was wondering if there was a way to do this with a single formula that would return:

9, 8, 6, 5, 5

into a single cell?

Another question, instead of printing them out, I would also like to SUM the numbers in a different cell.

1

You can use TEXTJOIN() as an array formula.

=TEXTJOIN(",",TRUE,LARGE(A1:G1,ROW($1:$5)))

The ROW($1:$5) when entered as an array returns the array {1,2,3,4,5} returning the top five largest numbers to the TEXTJOIN.

You force the Array by confirming the formula with Ctrl-Shift-Enter instead of Enter when Exiting Edit mode.

TEXTJOIN is available in Office 365 Excel.

To get the sum we can use the same LARGE inside a SUMPRODUCT:

=SUMPRODUCT(LARGE(A1:G1,ROW($1:$5)))

enter image description here

For older versions you are stuck with:

=LARGE(A1:G1, 1) & "," & LARGE(A1:G1, 2) & "," & LARGE(A1:G1, 3) & "," & LARGE(A1:G1, 4) & "," & LARGE(A1:G1, 5) 

Or helper cells.

  • Thank you. I also figured out the answer to my second question. =SUMPRODUCT(LARGE(A1:H1,{1,2,3,4,5})) – Bobby S Apr 12 '18 at 19:10
  • @BobbyS nevermind just finally read the last sentence. – Scott Craner Apr 12 '18 at 19:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.