Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find a result in an array in the same row as a minimum resu
I have temperature data for many years and am trying to find the minimum
temperature for a given day. I can find the min temperature on a given day with this: {=MIN(IF(TEXT($A$7:$A$2001,"mmm,d")=X127,$D$7:$D$2 001))} where X127 has the day of the month I'm searching for and the low temperatures are in column "D". But now I want the day it occurred which is in column "A" of the same row as the min temp. How do I get that? I thought this would work; {=ADDRESS(ROWS(D7:D2001=Z127),1)} but that gives me an erroneous address. Another question: If I have the correct result from the 'ADDRESS()' function above, how do I access the data in that cell that is referenced as a text value? Thanks for your help! Doug |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find a result in an array in the same row as a minimum resu
Use Index/match - set for an exact match
The Index part of it is the col result that you want returned You could try something like this, array-entered* (untested): =INDEX($A$7:$A$2001,MATCH(MIN(IF(TEXT($A$7:$A$2001 ,"mmm,d")=X127,$D$7:$D$2001)),IF(TEXT($A$7:$A$2001 ,"mmm,d")=X127,$D$7:$D$2001),0)) *press CTRL+SHIFT+ENTER to confirm the formula Success? wave it, hit YES below -- Max Singapore --- "dbasmb" wrote: I have temperature data for many years and am trying to find the minimum temperature for a given day. I can find the min temperature on a given day with this: {=MIN(IF(TEXT($A$7:$A$2001,"mmm,d")=X127,$D$7:$D$2 001))} where X127 has the day of the month I'm searching for and the low temperatures are in column "D". But now I want the day it occurred which is in column "A" of the same row as the min temp. How do I get that? I thought this would work; {=ADDRESS(ROWS(D7:D2001=Z127),1)} but that gives me an erroneous address. Another question: If I have the correct result from the 'ADDRESS()' function above, how do I access the data in that cell that is referenced as a text value? Thanks for your help! Doug |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find a result in an array in the same row as a minimum
Max:
Yeah!! That's the ticket!!! Thanks for your help. Is there any easier way to answer the last part of my question?? Given an address in text, such as '$A$2000', how do I use that to get the contents of $A$2000?? Thanks again, Doug |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to find a result in an array in the same row as a minimum
In B1 you have the text $A$2000
In C1 enter =INDIRECT(B1) Gord Dibben MS Excel MVP On Wed, 5 May 2010 13:46:02 -0700, dbasmb wrote: Max: Yeah!! That's the ticket!!! Thanks for your help. Is there any easier way to answer the last part of my question?? Given an address in text, such as '$A$2000', how do I use that to get the contents of $A$2000?? Thanks again, Doug |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How Do I set a minimum(or default) value a a result from formula | Excel Discussion (Misc queries) | |||
Minimum array formula. | Excel Worksheet Functions | |||
Array Formula Minimum Excluding Text | Excel Discussion (Misc queries) | |||
How do I set a minimum result value in an Excel formula? IE 100 | Excel Worksheet Functions | |||
Finding minimum value across selected rows of an array | Excel Worksheet Functions |