Posted to microsoft.public.excel.worksheet.functions
|
|
(Part 3) - Retrieve Numeric Label for Max Value by Criteria across Single Row
Hi Domenic,
Thank you very much. That's absolutely Brilliant!!
Cheers,
Sam
Domenic wrote:
Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER...
For the first one...
E2:
=MAX(INDEX(GrowthA!$C$2:$N$31,0,MATCH(MONTH($D2), MONTH(GrowthA!$C$1:$N$1)
,0)))
F2:
=COUNTIF(INDEX(GrowthA!$C$2:$N$31,0,MATCH(MONTH($ D2),MONTH(GrowthA!$C$1:$
N$1),0)),$E2)
G2, copied across:
=IF(COLUMNS($G2:G2)<=$F2,INDEX(GrowthA!$A$2:$A$31 ,SMALL(IF(INDEX(GrowthA!
$C$2:$N$31,0,MATCH(MONTH($D2),MONTH(GrowthA!$C$1: $N$1),0))=$E2,ROW(Growth
A!$A$2:$A$31)-ROW(GrowthA!$A$2)+1),COLUMNS($G2:G2))),"")
For the second one...
E6:
=MAX(INDEX(GrowthB!$C$5:$AO$438,MATCH(1,IF(DAY(Gr owthB!$A$5:$A$438)=DAY($
D6),IF(MONTH(GrowthB!$B$5:$B$438)=MONTH($D6),1)), 0),0))
F6:
=COUNTIF(INDEX(GrowthB!$C$5:$AO$438,MATCH(1,IF(DA Y(GrowthB!$A$5:$A$438)=D
AY($D6),IF(MONTH(GrowthB!$B$5:$B$438)=MONTH($D6), 1)),0),0),$E6)
G6, copied across:
=IF(COLUMNS($G6:G6)<=$F6,INDEX(GrowthB!$C$4:$AO$4 ,SMALL(IF(INDEX(GrowthB!
$C$5:$AO$438,MATCH(1,(DAY(GrowthB!$A$5:$A$438)=DA Y($D6))*(MONTH(GrowthB!$
B$5:$B$438)=MONTH($D6)),0),0)=$E6,COLUMN(GrowthB! $C$4:$AO$4)-COLUMN(Growt
hB!$C$4)+1),COLUMNS($G6:G6))),"")
Hope this helps!
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1
|