Anyone know how to select the 3rd highest num...

(Simon Robinson) #1

Anyone know how to select the 3rd highest number from a list?

(Suvrutt Gurjar) #2

Hi @Simon_Robinson,Please try

MIN(TOP((SORT([List Column],TRUE)),3))

(Bellave Jayaram) #3

Try Max([Numbers] - {Max([Numbers]),Max([Numbers] - {Max([Numbers])})})

(Reza Raoofi) #4

Well, I am guessing the numbers are not necessarily unique numbers, are they? I tested different combination of expressions, and finally the only way I could make it work was as follows:

Assuming in Students table [ID] is Key column, and [Score] (which can be duplicate) is the third highest number that we need, creating 3 Virtual Columns with formulas below gave me the correct result:

  1. [Sorted] = ORDERBY(Students[ID],[Score],true) 2) [Distinct] = SELECT([Sorted][Score],true,true) 3) [ThirdHighest] =MIN(TOP([Distinct],3))

If the scores cannot be duplicate you can drop virtual column #2, (i.e. [Distinct]).

(Suvrutt Gurjar) #5

Hi @RezaRaoofi That is really

a thorough thought process (that numbers may not be unique)

and your solution is also equally detailed. Kudos !

(Reza Raoofi) #6

@Suvrutt_Gurjar thanks!


(Gil Littman [AppSheet]) #7

You could also use the new INDEX() function to get the nth item of a sorted list. - INDEX() INDEX()

(Reza Raoofi) #8

@Gil_Littman_AppSheet Wow! When did INDEX() come out?! :slight_smile:

That would simplify and replace steps #3 with something like this:

[ThirdHighest] = INDEX([Distinct],3)

We used to get an update on new features/functions here in community; I hope that good old tradition is still in place. :slight_smile:

(Gil Littman [AppSheet]) #9

@RezaRaoofi About 2 weeks ago. I guess we didn’t do a very good job communicating it.

(Reza Raoofi) #10

I see.

Yea, new functions have a history of being missed out in feature updates and documentation, but I see the recent improvement in functions documentation format which looks nice! :+1: