Top 5 absolute values

I have a data set and I am trying to create a formula to pull top 5 values based on the second column. The out put should be top 5 (omitting 0s) values from the second column.

Thank you, Vitaliy

2 Answers

You can use either of the following. I saw no tag for earlier versions so I set them up using functions from the current set and taking advantage of SPILL functionality.

=INDEX(SORTBY(FILTER(B1:B9,B1:B9<>0),ABS(FILTER(B1:B9,B1:B9<>0)),-1,FILTER(B1:B9,B1:B9<>0),-1),SEQUENCE(5),1)

Pretty straightforward. FILTER() removes the 0's. SORTBY() sorts by two "columns" to use regular sorting terminology. The first is by the absolute values of the source range, in descending order. That sees "5" and "-5" as the same so it groups them together giving you: {-5,5,3,-2,2,-1,-1,1} since the "0" is gone and that is the order of the rest of the values in the source range, at each "value point" ("-5" is encounter before "5" so they come in as {-5,5}, and so on).

The second sort order piece of SORTBY() puts all the values that have identical absolute values in their "natural" order. So the 5's elements (two of those) change from {-5,5} to {5,-5}, on down to the three 1's elements changing from {-1,-1,1} to {1,-1,-1} giving you {5,-5,3,2,-2,1,-1,-1} as SORTBY()'s final array to pass on to `INDEX().

SORTBY()'s purpose was to produce a list in order from the largest by absolute value to the smallest AND then place them in natural order.

INDEX() is used simply to select the first five elements as they are the five whose absolute values were the highest. The only "tricky" thing in it is using SEQUENCE() to produce the number of rows (elements) desired from SORTBY()'s list.

Note that formula "judges" the individual absolute value source elements: it chooses "1" over either "-1" even though a "-1" came first and their absolute values are the same. I decided that was your intention. And, to be honest, it gave a nicer formula... However, you might want the one that appears higher in the source range to be returned rather than having them sorted so that the one of each value that was positive to start with is returned.

The below might be a version you would use with the LET() function lending a helping hand:

=LET(ResultCount, 5, Values, B1:B9, CleanValues, FILTER(Values,Values<>0), INDEX( SORTBY(CleanValues, ABS(CleanValues),-1, CleanValues,-1 ), SEQUENCE(ResultCount), 1) )

Aside from being easier in some ways (harder in others) to maintain and change its exact use, it puts the two values one might wish to change now and then at the very start, easy to find and easy to edit. Just one edit for either and it's reflected throughout. No need to hunt for the values you wish to change, or find every last instance of either in order to do so. If one appears 30 times, you still only need one edit in an easy to find place.

This find Top 5 absolute value:

• An array (CSE) formula in cell M93:

  {=IF(ROW(A1)<=5,LARGE(K$93:K$103,ROWS($A$1:A1)),"")}

N.B.

• Finish formula with Ctrl+Shift+Enter.
• In above formula If(Row(A1)<=5 is adjustable.
• Adjust cell references in the formula as needed.