0

In a row containing numbers, I need to get the address of that cell that contains the maximum value of the row.

I wish to find a local minimum. In my case it is the minimum value following after the global maximum. So I tried to get the line number of the cell with the global maximum, so I can start searching from there for MIN till to the last value in the row for the MIN. I with ADDRESS combined with MAX - failed.

=ADRESS(MAX(D2:D3600);4;1;1) ...in order to check the 4th row "D" for the line number of the cell containing the maximum value.

In my case the global maximum value is 900, and it is found in line 830.

So I need to get $D$830 and not $D$900

gloschtla
  • 41
  • 3
  • 10

1 Answers1

0

This will find the minimum value after the maximum value in column D

=MIN(INDEX(D:D,MATCH(MAX(D:D),D:D,0)):D1048576)
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • indeed, thank you a lot. i used the excel translator, so it works in my local language. i my case the excel formula works like this: =MIN(INDEX(D:D;VERGLEICH(MAX(D:D);D:D;0)):D1048576) – gloschtla Jan 30 '19 at 14:48