| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference for MAXIF array formula
The array formula MAXIF correctly gives me the maximum
value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How can I find the corresponding value in column D in A1:H99, i.e. in this case Sheet2!D43? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference for MAXIF array formula
You have a reply to one of your threads in .misc
JAK wrote: The array formula MAXIF correctly gives me the maximum value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How can I find the corresponding value in column D in A1:H99, i.e. in this case Sheet2!D43? -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference for MAXIF array formula
You would need an array formula.
What are the conditions in your maxif formula? -- Regards, Tom Ogilvy "JAK" wrote in message ... The array formula MAXIF correctly gives me the maximum value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How can I find the corresponding value in column D in A1:H99, i.e. in this case Sheet2!D43? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference for MAXIF array formula
Tom
I posted a simplified version of my query. Here is a more complete version: The formula {=MAX((TEXT(Data!A$1:A$1000,"dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000))} correctly gives me the maximum value of Data! column B subject to a specified date (format dd mmm) in columns A of the current and data! sheets. That works well. My problem is that while I am interested in the maximum value returned from Data!B1:B1000 I also want the correspoding value in another column in the full table Data!A1:H1000, e.g. the value in Data column H in the same row as the maximum value returned from Data column D. I would welcome help from any reader. "Tom Ogilvy" wrote: You would need an array formula. What are the conditions in your maxif formula? -- Regards, Tom Ogilvy "JAK" wrote in message ... The array formula MAXIF correctly gives me the maximum value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How can I find the corresponding value in column D in A1:H99, i.e. in this case Sheet2!D43? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference for MAXIF array formula
=INDEX(Data!$H$1:$H$1000,MATCH(MAX((TEXT(Data!A$1: A$1000,"dd
mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000, "dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1) Your formula, as posted/written returns the maximum value in Column B for those rows matching the date. You would change the $B$1:$B$1000 to $D$1:$D$1000 if you want to find the row based on the maximum value in column D. The value returned is from column H. -- Regards, Tom Ogilvy "JAK" wrote in message ... Tom I posted a simplified version of my query. Here is a more complete version: The formula {=MAX((TEXT(Data!A$1:A$1000,"dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000))} correctly gives me the maximum value of Data! column B subject to a specified date (format dd mmm) in columns A of the current and data! sheets. That works well. My problem is that while I am interested in the maximum value returned from Data!B1:B1000 I also want the correspoding value in another column in the full table Data!A1:H1000, e.g. the value in Data column H in the same row as the maximum value returned from Data column D. I would welcome help from any reader. "Tom Ogilvy" wrote: You would need an array formula. What are the conditions in your maxif formula? -- Regards, Tom Ogilvy "JAK" wrote in message ... The array formula MAXIF correctly gives me the maximum value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How can I find the corresponding value in column D in A1:H99, i.e. in this case Sheet2!D43? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference for MAXIF array formula
Tom
Thanks for your help so far I apologise for not being clear enough. I am looking for the value in Column D that corresponds with the maximum value in Column B- not the maximum value in Row D. I could do that easily if I could return the reference to the maximum value in Column B as well as the maximim value, because that would identify the row number with the maximim B-value but have failed to see how to do that. Regards JAK "Tom Ogilvy" wrote: =INDEX(Data!$H$1:$H$1000,MATCH(MAX((TEXT(Data!A$1: A$1000,"dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000, "dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1) Your formula, as posted/written returns the maximum value in Column B for those rows matching the date. You would change the $B$1:$B$1000 to $D$1:$D$1000 if you want to find the row based on the maximum value in column D. The value returned is from column H. -- Regards, Tom Ogilvy "JAK" wrote in message ... Tom I posted a simplified version of my query. Here is a more complete version: The formula {=MAX((TEXT(Data!A$1:A$1000,"dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000))} correctly gives me the maximum value of Data! column B subject to a specified date (format dd mmm) in columns A of the current and data! sheets. That works well. My problem is that while I am interested in the maximum value returned from Data!B1:B1000 I also want the correspoding value in another column in the full table Data!A1:H1000, e.g. the value in Data column H in the same row as the maximum value returned from Data column D. I would welcome help from any reader. "Tom Ogilvy" wrote: You would need an array formula. What are the conditions in your maxif formula? -- Regards, Tom Ogilvy "JAK" wrote in message ... The array formula MAXIF correctly gives me the maximum value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How can I find the corresponding value in column D in A1:H99, i.e. in this case Sheet2!D43? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference for MAXIF array formula
Tom
My apologies. Your formula looks good Jim, from South EastQueensland "Tom Ogilvy" wrote: What's not clear about: e.g. the value in Data column **H** in the same row as the maximum value returned from Data column **D**. =INDEX(Data!$D$1:$D$1000,MATCH(MAX((TEXT(Data!A$1: A$1000,"dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000, "dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1) The only adjustment was to change the H's to D's -- Regards, Tom Ogilvy "James Kelly" <James wrote in message ... Tom Thanks for your help so far I apologise for not being clear enough. I am looking for the value in Column D that corresponds with the maximum value in Column B- not the maximum value in Row D. I could do that easily if I could return the reference to the maximum value in Column B as well as the maximim value, because that would identify the row number with the maximim B-value but have failed to see how to do that. Regards JAK "Tom Ogilvy" wrote: =INDEX(Data!$H$1:$H$1000,MATCH(MAX((TEXT(Data!A$1: A$1000,"dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),((TEXT(Data!A$1:A$1000, "dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000)),0),1) Your formula, as posted/written returns the maximum value in Column B for those rows matching the date. You would change the $B$1:$B$1000 to $D$1:$D$1000 if you want to find the row based on the maximum value in column D. The value returned is from column H. -- Regards, Tom Ogilvy "JAK" wrote in message ... Tom I posted a simplified version of my query. Here is a more complete version: The formula {=MAX((TEXT(Data!A$1:A$1000,"dd mmm")=TEXT(A8,"dd mmm"))*(Data!$B$1:$B1000))} correctly gives me the maximum value of Data! column B subject to a specified date (format dd mmm) in columns A of the current and data! sheets. That works well. My problem is that while I am interested in the maximum value returned from Data!B1:B1000 I also want the correspoding value in another column in the full table Data!A1:H1000, e.g. the value in Data column H in the same row as the maximum value returned from Data column D. I would welcome help from any reader. "Tom Ogilvy" wrote: You would need an array formula. What are the conditions in your maxif formula? -- Regards, Tom Ogilvy "JAK" wrote in message ... The array formula MAXIF correctly gives me the maximum value of A in a separate sheet in my table Sheet2!A1:H99, say A43. How can I find the corresponding value in column D in A1:H99, i.e. in this case Sheet2!D43? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Array Formula + Indirect Reference | Excel Worksheet Functions | |||
| Array Constant: How do I reference each value in a formula | New Users to Excel | |||
| How to do a MAXIF formula | Excel Worksheet Functions | |||
| Array formula reference | Excel Discussion (Misc queries) | |||
| Array formula reference (Corrected) | Excel Discussion (Misc queries) | |||
Linear Mode
