Thread
:
Reference for MAXIF array formula
View Single Post
#
8
Posted to microsoft.public.excel.programming
James Kelly[_4_]
external usenet poster
Posts: 1
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 With Quote
James Kelly[_4_]
View Public Profile
Find all posts by James Kelly[_4_]