Cell Reference of Max Num. or Min Num.

J

JKJ95

I am trying to have Excel give me the Cell Reference of the Max. or Min
number in a range of cells. Once I have this, I would like to incorporate the
reference in the Offset function. Is this possible?
 
P

Peo Sjoblom

How would you like to incorporate it into offset?

=CELL("address",INDEX(A2:A500,MATCH(MAX(A2:A500),A2:A500,0)))

will give you a cell reference but if you need it to be incorporated into
offset you only need

=OFFSET($A$2,MATCH(MAX(A2:A500),A2:A500,0)-1,,,)

if you want to use that cell as starting point and let's say sum 20 cells
from that use

=SUM(OFFSET($A$2,MATCH(MAX(A2:A500),A2:A500,0)-1,,20,))
 
D

Duke Carey

One way - (assumes your data is in B2:B4)

=OFFSET(INDEX(B2:B4,MATCH(MAX(B2:B4),B2:B4,0)),0,1)
 
Top