return the column reference number of a function result

[Guest]

I use the MIN(C49:GL49) function to get the minimum value contained in the
cell. Please tell me how to get the cell reference of the minimum value
rather than the value contained in the cell.

thanks
Mahendhra

 

[William]

Hi Mahendhra

=ADDRESS(49,2+MATCH(MIN(C49:GL49),C49:GL49,0))
--
XL2003
Regards

William
(e-mail address removed)

 

[RagDyeR]

Since you already know that the row number is 49, try this *array* formula:

=ADDRESS(49,MAX((C49:GL49=MIN(C49:GL49))*COLUMN(C49:GL49)))

If you're going to perhaps copy this down, to find the minimum column in a
datalist, adjust the formula to this:

=ADDRESS(ROW(),MAX((C49:GL49=MIN(C49:GL49))*COLUMN(C49:GL49)))

Ties will return the column with the highest address number.
--
Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I use the MIN(C49:GL49) function to get the minimum value contained in the
cell. Please tell me how to get the cell reference of the minimum value
rather than the value contained in the cell.

thanks
Mahendhra