return cell address

**missing** · 12-14-2007, 06:24 AM

Hello, i have a problem. I need to return cell address of MIN value:
example:

A B C
1 5 2 7
2 4 9 3
3 10 1 5

Result = B3

My function:

=ADDRESS(MIN(IF(A1:C3=MIN(A1:C3),ROW(A1:C3);""));COLUMN(A1:C3))

it return #VALUE

Where is a mistake?

**davesexcel** · 12-14-2007, 06:48 AM

Address Of First Minimum In A Range

To return the address of the cell containing the first (or only) instance of the minimum of a list,
use the following array formula:

=ADDRESS(MIN(IF(NumRange=MIN(NumRange),ROW(NumRange))),COLUMN(NumRange),4)

This function returns B2, the address of the first '1' in the range.

http://www.cpearson.com/excel/excelF.htm

this is an array formula you will be required to
press ctrl shift enter ( instead of just enter)
to execute it, don't know if it works for various columns though

**missing** · 12-14-2007, 03:17 PM

so i wrote
=ADDRESS(MIN(IF(NumRange=MIN(NumRange);ROW(NumRang e)));COLUMN(NumRange),4)

and result=#NAME

if i wrote
=ADDRESS(MIN(IF(A1:C3=MIN(A1:C3);ROW(A1:C3)));COLUMN(A1:C3),4)
result=#VALUE

what i do wrong?

**missing** · 12-17-2007, 02:58 AM

help

**davesexcel** · 12-17-2007, 04:14 AM

Originally Posted by missing

help

This site will help
http://support.microsoft.com/kb/213375

the code below is what you want

Please Login or Register  to view this content.

hit alt F11 then insert,module, copy and paste the code there
next use the formula
=minaddress(A1:C3)
to get the address of the min value of that range

**daddylonglegs** · 12-17-2007, 08:20 AM

This formula will give you the address in the format $B$3. If there are 2 or more values tied for MIN then it looks at rows before columns so if A2 and B1 were tied for the minimum it gives B1.

=ADDRESS(MIN(IF(MIN(A1:C3)=A1:C3,ROW(A1:C3)-ROW(A1)+1)),MATCH(MIN(A1:C3),INDEX(A1:C3,MIN(IF( MIN(A1:C3)=A1:C3,ROW(A1:C3)-ROW(A1)+1)),0),0))

confirmed with CTRL+SHIFT+ENTER

You can do it more easily if you can guarantee the the min value occurs only once?

What do you need this for? Often people get the address as a step on the way to do something else, if that's true for you then I'd advise you not to do it this way......