Cell Reference of Max Num. or Min Num.
 

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?

How would you like to incorporate it into offset?

=CELL("address",INDEX(A2:A500,MATCH(MAX(A2:A500),A 2: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,))


--
Regards,

Peo Sjoblom


"JKJ95" wrote in message
...
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?

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

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


"JKJ95" wrote:

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?