Reference to a cell returned by MIN...how?

[Mac]

How do I get to the row and col reference of a cell whose value I obtain via
calling MIN (I need to input this referece to OFFSET...)

 

[Mike H]

Hi,

You could try:-

=ADDRESS(MATCH(MIN(A1:A100),A1:A100,0),1)

Where the range you are looking for the minimum in is A1 -A100

Mike

 

[Mac]

Well, my range is a non-contiguous space, like R18C2, R18C10, R37C2,
R37C10..how to go around about this constraint? Apparently MATCH does not
like my range being scattered around...

 

[vezerid]

You can use a separate are to store the cell addresses, say in I1:I4.
Then, assuming that the rectangular range that contains all these
cells is A15, you can use:

=MIN(IF(ISNUMBER(MATCH(ADDRESS(ROW(A15),COLUMN(A15),
4,0),I1:I4,0)),A15))

Enter as an *array* formula (Shift+Ctrl+Enter)

Note that I used the form ADDRESS(row,col,4,1). The 1 is if you store
the addresses as R1C1. You might need to modify the other references.

HTH
Kostis Vezerides