Finding a minimum value and selecting the cell containing this val

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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(B4: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)),SUMPRODUCT((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
 
Thanks for the feedback.....I'm glad that worked for you.


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

XL2002, WinXP-Pro
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Payment calculation 1
Stripe out duplicate data 5
Using IF function then Autofill-increments down a reference cell 2
Hiding sum formula answers in select cells 5
Divide by the minimum non-zero number in a range of cells 21
What is the problem with this formula? 5
How do I find the cell reference that contains the minimum or maximum 4
Trying to find additional ranges outside of min, max, and average 2

Back
Top