How to find the LOCATION of the K'th largest value of a range in Excel?

The question is as in the title. Note that LARGE(range, k) doesn't tell me the location of the value, and attempting to search for it using INDEX can fail in the presence of duplicates. Is there another way?

Answers


Depending on the original data and whether you're in a position to modify the workbook, one way is to use a "helper column" to give you unique values by adding a very small row-dependent value to your data, eg the row number divided by 1000000. As long as the additional value isn't greater than the smallest true difference in data values it will not change the rank ordering of the data, but it does make it unique and has the benefit of being relatively "easy".

In this example B3:B17 contains mostly integer values, a couple of ".5" fractions, with some duplication. I have set C3:C17 as the helper column, in which I'm adding and adjustment value of the row number * 0.0000001. As the smallest step change in the genuine data is much larger than the largest adjustment value, there is no risk of incorrect ordering - only an imposition that where the original data is exactly the same, ordering is now based on location in the table.

Column F lists the largest helper values (eg cell F3 formula "=LARGE($C$3:$C$17,E3)"), column G finds the row that value is in (cell G3 formula "=MATCH(F3,C:C,0)"), and then your output table looks in the genuine data using this row number.


what if you use one column and type=IFERROR(RANG.eq(K2;"range of K")+SUMPRODUCT(--(K2=K$2:K$74"ex");--(K2<K$2:K$74));"") and one column =IFERROR(COUNTIFS(M2"new column":$M$74;M2)+M2-1;""). After that all numbers in K will be ranged even if equal and you can use LARGE


Need Your Help

worth to learn groovy?

language-agnostic groovy

the question im asking, is it worth to learn a new language like groovy? cause if i learn groovy, it feels like i code in groovy and not java. and how smart is that when i have to be good in java t...

Host-based Card Emulation with Fixed Card ID

android nfc android-4.4-kitkat uniqueidentifier hce

Android 4.4 introduced Host-based Card Emulation (HCE). As you know, all NFC cards come with a fixed card ID (NfcAdapter.EXTRA_ID).