returning a cells address based on a formula?

D

Dave

I have a data range of a1:d5 with sales figures. I have determined the
lowest value using =MIN(A1:D5).
What I need to know is what cell contains that lowest value (is it a3
or b2 or d4, etc.).
The =CELL("address",min(a1:d5)) does NOT work. Any idaes?
Thanks, DAVE
 
B

Bernie Deitrick

Dave,

Use the array formula, entered with Ctrl-Shift-Enter:

=ADDRESS(MAX((A1:D5=MIN(A1:D5))*ROW(A1:D5)),MAX((A1:D5=MIN(A1:D5))*COL
UMN(A1:D5)))

HTH,
Bernie
MS Excel MVP
 
B

Bernie Deitrick

Dave,

I should have noted that this formula may fail if the minimum value is not
unique (and if one of the duplicate values has a higher row and lower column
than the other....).

The formula can be fixed to check for duplicate minimums and return an error
rather than a possibly incorrect address, if that is a concern.

HTH,
Bernie
MS Excel MVP
 
Top