Posted to microsoft.public.excel.worksheet.functions
|
|
(Part 2) - Retrieve Numeric Label for Max Value by Specific Day & Month
Hi Domenic,
Thank you for reply and assistance. Formula provides expected results. Great!
How do I return the Maximum value from the same set of data rather than the
Numeric Label?
Cheers,
Sam
Domenic wrote:
Assuming that A2 contains the day of interest (true date value formatted
as "dd"), and B2 contains the month of interest (true date value
formatted as "mmm"), try the following formula which needs to be
confirmed with CONTROL+SHIFT+ENTER...
=INDEX(C4:AO4,MATCH(MAX(INDEX(C5:AO436,MATCH(1,IF (DAY(A5:A436)=DAY(A2),IF
(MONTH(B5:B436)=MONTH(B2),1)),0),0)),INDEX(C5:AO4 36,MATCH(1,IF(DAY(A5:A43
6)=DAY(A2),IF(MONTH(B5:B436)=MONTH(B2),1)),0),0), 0))
This works Great!
If, however, A2 contains the day of interest, such as 1, 2, 3, etc., and
B2 contains the month of interest, such as Jan, Feb, Mar, etc., try the
following instead...
=INDEX(C4:AO4,MATCH(MAX(INDEX(C5:AO436,MATCH(1,IF (DAY(A5:A436)=A2,IF(TEXT
(B5:B436,"mmm")=B2,1)),0),0)),INDEX(C5:AO436,MATC H(1,IF(DAY(A5:A436)=A2,I
F(TEXT(B5:B436,"mmm")=B2,1)),0),0),0))
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1
|