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

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...)

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

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" wrote:

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...)

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

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...

"Mike H" wrote:

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" wrote:

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...)

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

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 A1:D5, you can use:

=MIN(IF(ISNUMBER(MATCH(ADDRESS(ROW(A1:D5),COLUMN(A 1:D5),
4,0),I1:I4,0)),A1:D5))

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

On Oct 16, 1:28 pm, Mac wrote:
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...

"Mike H" wrote:
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" wrote:

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...)