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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...)
 
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
 
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...
 
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(A1: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
 
Back
Top