3

I have a table with numeric values within C20:G30. I would like to extract the top 10 values via MAX(LARGE($BG$26:$BT$69;1)) MAX(LARGE($BG$26:$BT$69;2)), etc.

That I managed to do. Then, I would like to retrieve the addresses of these top 10 values. It works if I do

INDIRECT(ADDRESS(ROW(E25); COLUMN(E25))

but it does not if I substitute the cell coordinates E25 with

MAX(LARGE($BG$26:$BT$69;1)).

How could I make all this work directly? Obviously INDIRECT(ADDRESS(ROW(MAX(LARGE($BG$26:$BT$69;1))); COLUMN(MAX(LARGE($BG$26:$BT$69;1)))) seems not to be correct and excel tells me the formula is wrong? Let me know if I should re-explain the question if it was not clear. enter image description here

Martin G
  • 33
  • 5
  • 1
    Please show what you have in data and your actual end expected outcome. I venture to guess you want the address to pull some other data on the same row or column. as a side not you do not need the MAX() wrapper on the LARGE() formula, the LARGE() only returns one number and so the MAX() is not needed. – Scott Craner May 20 '16 at 18:34
  • 2
    Couple things, wrapping `max` around `large` doesn't do anything as `large` returns a single value. Secondly `large` returns a value not a cell address so it can't be used in the indirect function. [here](https://support.microsoft.com/en-us/kb/139574) is microsoft support on how to find the cell address of the max value, this could be expanded to find the other large values. – gtwebb May 20 '16 at 18:34
  • Thank you very much for your prompt replies! My goal would be to obtain D12:D17. I see your answer below, Scott Craner, and thank you very much, that must resolve the problem. I will get back to you as soon as I manage to solve the issue! :) – Martin G May 20 '16 at 19:19
  • See my edit below. – Scott Craner May 20 '16 at 19:22

1 Answers1

1

As was said in the comments above You do not need the MAX() wrapper. LARGE() only returns a single number. Something like this will work:

=LARGE($A$2:$E$21,ROW(1:1))

The ROW(1:1) is a counter that as it is dragged down will increment, so each formula does not need to be changed.

The following formula will return the address of the list created with the above formula:

=ADDRESS(AGGREGATE(15,6,ROW($A$2:$E$21)/($A$2:$E$21=G2),COUNTIF($G$2:$G2,G2)),AGGREGATE(15,6,COLUMN($A$2:$E$21)/(($A$2:$E$21=G2)*(AGGREGATE(15,6,ROW($A$2:$E$21)/($A$2:$E$21=G2),COUNTIF($G$2:$G2,G2))=ROW($A$2:$E$21))),1))

It will deal with numbers that duplicate.

enter image description here

EDIT

Using the photo you just provided the amended formula would be:

=ADDRESS(AGGREGATE(15,6,ROW($A$2:$E$21)/($A$2:$E$21=G2),COUNTIF($G$2:$G2,G2)),AGGREGATE(15,6,COLUMN($A$2:$E$21)/(($A$2:$E$21=G2)*(AGGREGATE(15,6,ROW($A$2:$E$21)/($A$2:$E$21=G2),COUNTIF($G$2:$G2,G2))=ROW($A$2:$E$21))),1))

Put that in E13 and copy down.

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Thank you so much! Incredible how quickly you solved my problem. I was playing around max, address, column, aggregate, index and match whole day! Thank you very much! :) – Martin G May 20 '16 at 19:43
  • @MartinG Please mark as correct by clicking the check mark by the answer. It is something only you can do. – Scott Craner May 20 '16 at 19:46
  • Please, excuse me for not doing it before you prompted! – Martin G May 20 '16 at 19:59