Good point....and nice catch. Thanks!
My comment about the first min value was incorrect.

I think the point I was trying to make was this:
In it's most vanilla scenario (only one min value), the formula is pretty
darn long and complications would only make it more unwieldy.

Hmmmm....seems like I could have just said that in the first place, eh?

Anyway, since we're on the topic, here's the formula for finding the first
occurrence of the minimum value in a range referenced by text in Cell A1:

=ADDRESS(SUMPRODUCT(MIN(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*ROW(INDIRECT(A1 ))+(((INDIRECT(A1))<MIN(INDIRECT(A1)))*10^99))),S UMPRODUCT(MIN(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*COLUMN(INDIRECT (A1))+(((INDIRECT(A1))<MIN(INDIRECT(A1)))*10^99)) ))

....and THAT only works as long as the minimum is not zero when there are
blank cells in the range (which would equate to zero).

***********
Regards,
Ron


"B. R.Ramachandran" wrote:

Ron,

A modification is needed to handle duplicate occurrence of the range-minimum.

I also thought of a similar formula first, but realized that the formula
won't work if there are duplicate minimum values; it will not return the
location of the first occurrence of the minimum; it would, on the other hand,
ADD all the row numbers of cells containing the minimum, and similarly add
the corresponding column numbers, and return an incorrect cell address as the
answer. For example, if B1 and B2 contain the range-minimum, the formula
will return "D3" which corresponds to ADDRESS(3,4).

Regards,
B. R. Ramachandran

"Ron Coderre" wrote:

If you only want to use out-of -the-box Excel functions...try this:

A1: (some range reference, like B1:L10)
A2:
=ADDRESS(SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*ROW(INDIRECT(A1 ))),SUMPRODUCT(--((INDIRECT(A1))=MIN(INDIRECT(A1)))*COLUMN(INDIRECT (A1))))

That will return the address of the cell that contains the lowest value.
Note 1: If there are duplicate minimum values, It will return the address of
it will return the location of the first.

Note 2: for simplicity sake, the referenced range cannot contain Blank
Cells. If the range may contain blanks, the formula becomes more cumbersome.

Does that help?

***********
Regards,
Ron


"Mike H" wrote:

Hello, I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.

Any ideas?

Many thanks