Get address from MIN()

[Terry]

Is it possible to get an address or a range of cells from the MIN() function (or any other
comparable function for that matter), such that:

=OFFSET(MIN(C2:G2),-8,1)

would insert the contents of another cell in the location of the formula based on the lowest value
in another range of cells???

Regards,
Terry

 

[curllion]

U try the formula "match" or "index".
And study the help file about them.
--
=====================================================
* ÔÚÎÒÇà´ºÄêÉÙµÄʱºò£¬ÓиöÅ®º¢Ô¸ÎªÎÒÈ¥ËÀ¡£ ¡¡¡¡ *
* ËýÒâÖ¾¼á¶¨µØ¶ÔÎÒ˵£¬ÄãÒªÊÇÔÙ²ø×ÅÎÒ£¬ÎÒ¾ÍÈ¥ËÀ£¡ *
* ÔÚÎÒÇîÀ§Áʵ¹µÄʱºò£¬ÓиöÅ®ÈËÔ¸ºÍÎÒ¹²¸°»ÆȪ£¡¡¡¡¡ *
* ËýÕ¶¶¤½ØÌúµÄ¶ÔÎÒ˵£¬ÄãÔÙ²»»¹Ç®£¬ÎҾͺÍÄãͬ¹éÓÚ¾¡£¡*
* *
* ÕŽ¨Æ½ *
=====================================================

 

[Bob Phillips]

Terry,

Here is one way, although the -8 row from row 2 throws an error

=ADDRESS(2,COLUMN(INDEX(C2:G2,1,MATCH(MIN(C2:G2),C2:G2,0))))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
try
=INDEX(C3:G3,1,MATCH(MIN(C2:G2),C2:G2,0))
return the value from row 3

 

[Harlan Grove]

Here is one way, although the -8 row from row 2 throws an error

=ADDRESS(2,COLUMN(INDEX(C2:G2,1,MATCH(MIN(C2:G2),C2:G2,0))))

...

Why not

=CELL("Address",OFFSET(C2:G2,ROffs,MATCH(MIN(C2:G2),C2:G2,0))+COffs,1,1))

 

[Bob Phillips]

I actually tried that, but got an error. I must have had something wrong,
so I tried a different tack.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)