| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
