Address of minimum in range with #N/A

[MartyM]

Hello,

I have bee searching the Internet for this but cannot get an array formula to work this out.

I have a range of positive values (single column) and need to find the address of the cell holding the minimum of this range. This works for a range K60:K70, see below.

=ADDRESS(MIN(IF(K60:K70=MIN(K60:K70);ROW(K60:K70)));COLUMN(K60:K70);4)

Now, this range also contains #N/A. I tried various ISNA statements but the full evaluation fails since I always keep with a MIN function being evaluated against this #N/A.

Where do I put the ISNA check to make this work?

 

[Andrew Poulsom]

Welcome to the Board.

Try:

=ADDRESS(MIN(IF(IF(ISNA(K60:K70);"!";K60:K70)=MIN(IF(ISNA(K60:K70);"";K60:K70));ROW(K60:K70)));COLUMN(K60:K70);4)

 

[MartyM]

Thanks Andrew,

quite a formula but it makes sense to me. Thanks for the great answer.