Finding a minimum value and selecting the cell containing this val
 

Hi,
I have data in 2 ranges. I want to find the minimim value in these ranges
and have Excel then select the cell matching that value.

Here's my MIN function:
=MIN(B4:D8,B13:D17)

The result is located in C14. How do I get Excel to run this function, then
go and select C14?

Thanks.

Finding a minimum value and selecting the cell containing this val
 

If you want the address of the cell containing the minimum value in the
referenced ranges, try this:

A1:
=ADDRESS(MAX(SUMPRODUCT((B4:D8=MIN(B4:D8,B13:D17)) *ROW(B4:D8)),SUMPRODUCT((B13:D17=MIN(B4:D8,B13:D17 ))*ROW(B13:D17))),MAX(SUMPRODUCT((B4:D8=MIN(B4:D8, B13:D17))*COLUMN(B4:D8)),SUMPRODUCT((B13:D17=MIN(B 4:D8,B13:D17))*COLUMN(B13:D17))))

or
This might be a bit easier to follow:
A1: =MIN(B4:D8,B13:D17)
B1:
=ADDRESS(MAX(SUMPRODUCT((B4:D8=A1)*ROW(B4:D8)),SUM PRODUCT((B13:D17=A1)*ROW(B13:D17))),MAX(SUMPRODUCT ((B4:D8=A1)*COLUMN(B4:D8)),SUMPRODUCT((B13:D17=A1) *COLUMN(B13:D17))))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Kokomojo" wrote:

Hi,
I have data in 2 ranges. I want to find the minimim value in these ranges
and have Excel then select the cell matching that value.

Here's my MIN function:
=MIN(B4:D8,B13:D17)

The result is located in C14. How do I get Excel to run this function, then
go and select C14?

Thanks.

Finding a minimum value and selecting the cell containing this
 

Wow! Thanks Ron. You saved me an afternoon!
Cheers!

"Ron Coderre" wrote:

If you want the address of the cell containing the minimum value in the
referenced ranges, try this:

A1:
=ADDRESS(MAX(SUMPRODUCT((B4:D8=MIN(B4:D8,B13:D17)) *ROW(B4:D8)),SUMPRODUCT((B13:D17=MIN(B4:D8,B13:D17 ))*ROW(B13:D17))),MAX(SUMPRODUCT((B4:D8=MIN(B4:D8, B13:D17))*COLUMN(B4:D8)),SUMPRODUCT((B13:D17=MIN(B 4:D8,B13:D17))*COLUMN(B13:D17))))

or
This might be a bit easier to follow:
A1: =MIN(B4:D8,B13:D17)
B1:
=ADDRESS(MAX(SUMPRODUCT((B4:D8=A1)*ROW(B4:D8)),SUM PRODUCT((B13:D17=A1)*ROW(B13:D17))),MAX(SUMPRODUCT ((B4:D8=A1)*COLUMN(B4:D8)),SUMPRODUCT((B13:D17=A1) *COLUMN(B13:D17))))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Kokomojo" wrote:

Hi,
I have data in 2 ranges. I want to find the minimim value in these ranges
and have Excel then select the cell matching that value.

Here's my MIN function:
=MIN(B4:D8,B13:D17)

The result is located in C14. How do I get Excel to run this function, then
go and select C14?

Thanks.

Finding a minimum value and selecting the cell containing this
 

Thanks for the feedback.....I'm glad that worked for you.


***********
Regards,
Ron

XL2002, WinXP-Pro


"Kokomojo" wrote:

Wow! Thanks Ron. You saved me an afternoon!
Cheers!

"Ron Coderre" wrote:

If you want the address of the cell containing the minimum value in the
referenced ranges, try this:

A1:
=ADDRESS(MAX(SUMPRODUCT((B4:D8=MIN(B4:D8,B13:D17)) *ROW(B4:D8)),SUMPRODUCT((B13:D17=MIN(B4:D8,B13:D17 ))*ROW(B13:D17))),MAX(SUMPRODUCT((B4:D8=MIN(B4:D8, B13:D17))*COLUMN(B4:D8)),SUMPRODUCT((B13:D17=MIN(B 4:D8,B13:D17))*COLUMN(B13:D17))))

or
This might be a bit easier to follow:
A1: =MIN(B4:D8,B13:D17)
B1:
=ADDRESS(MAX(SUMPRODUCT((B4:D8=A1)*ROW(B4:D8)),SUM PRODUCT((B13:D17=A1)*ROW(B13:D17))),MAX(SUMPRODUCT ((B4:D8=A1)*COLUMN(B4:D8)),SUMPRODUCT((B13:D17=A1) *COLUMN(B13:D17))))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Kokomojo" wrote:

Hi,
I have data in 2 ranges. I want to find the minimim value in these ranges
and have Excel then select the cell matching that value.

Here's my MIN function:
=MIN(B4:D8,B13:D17)

The result is located in C14. How do I get Excel to run this function, then
go and select C14?

Thanks.