Return Min & Cell address

[L. Howard]

Can this be done to return the MIN of F2, G2 or H2 and and "F", "G" or "H" which ever is the MIN?

Where F2=1, G2=2, F2=3.

This returns 1 FALSE.

=MIN(F2:H2)&" "&IF(ADDRESS(2,6)=MIN(F2:H2),"F",IF(ADDRESS(2,7)=MIN(F2:H2),"G",IF(ADDRESS(2,8)=MIN(F2:H2),"H")))

Thanks.
Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 29 Jul 2014 00:18:53 -0700 (PDT) schrieb L. Howard:

Can this be done to return the MIN of F2, G2 or H2 and and "F", "G" or "H" which ever is the MIN?

Where F2=1, G2=2, F2=3.

try:
=MIN(F2:G2)&" in "&ADDRESS(2,MATCH(MIN(F2:G2),A2:G2,0),4)


Regards
Claus B.

 

[Claus Busch]

Hi again,

Am Tue, 29 Jul 2014 09:27:53 +0200 schrieb Claus Busch:

=MIN(F2:G2)&" in "&ADDRESS(2,MATCH(MIN(F2:G2),A2:G2,0),4)

sorry, typo.

Try:
=MIN(F2:H2)&" in "&ADDRESS(2,MATCH(MIN(F2:H2),A2:H2,0),4)


Regards
Claus B.

 

[L. Howard]

Try:

=MIN(F2:H2)&" in "&ADDRESS(2,MATCH(MIN(F2:H2),A2:H2,0),4)





Regards

Claus B.

Never ceases to amaze me, thanks, Claus.

Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 29 Jul 2014 00:50:56 -0700 (PDT) schrieb L. Howard:

Never ceases to amaze me

always glad to help.

If the minimum can also occur in A2:E2 you will get a wrong cell
address.
In this case you better do the MATCH into the range for minimum:
=MIN(F2:H2)&" in "&ADDRESS(ROW(F2),MATCH(MIN(F2:H2),F2:H2,0)+COLUMN(F2)-1,4)

The formula is more versatile. You also can copy down the formula.


Regards
Claus B.

 

[L. Howard]

If the minimum can also occur in A2:E2 you will get a wrong cell

address.

In this case you better do the MATCH into the range for minimum:

=MIN(F2:H2)&" in "&ADDRESS(ROW(F2),MATCH(MIN(F2:H2),F2:H2,0)+COLUMN(F2)-1,4)



The formula is more versatile. You also can copy down the formula.





Regards

Claus B.

I can't make that error occur with the first formula.

With 1 to 8 in A - H both seem to work fine, each returns "6 in F2".

I'll go with the latter on your advice anyway.

Thanks again.
Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 29 Jul 2014 02:45:40 -0700 (PDT) schrieb L. Howard:

I can't make that error occur with the first formula.

perhaps I did not explain correctly.
Please look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Min"


Regards
Claus B.

 

[L. Howard]

Hi Howard,



Am Tue, 29 Jul 2014 02:45:40 -0700 (PDT) schrieb L. Howard:






perhaps I did not explain correctly.

Please look here:

https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

for "Min"





Regards

Claus B.

I understand now. The sheet explained it very well.

Thanks.

Howard