Hi All,

I very much appreciate all the help I have received with these formulae.

I would appreciate further assistance in adapting the following formulae to
Return across a Single
Row Multiple Numeric Labels that share the same Maximum value (duplicate
maximums).


Formula (1) Array Entered - Retrieve Numeric Label for Max Value by Specific
Month
Col "A" (A2:A31) = Numeric Labels
Col "C" - "N" (C1:N1) = Full Date formatted with Custom Month Format "mmm":
Jan - Dec
Col "C" - "N" (C2:N31) = Data Numeric Values

=INDEX(GrowthA'!$A$2:$A$31,MATCH(MAX(INDEX('Growth A'!$C$2:$N$31,0,MATCH(MONTH
($D2),MONTH(GrowthA'!$C$1:$N$1),0))),INDEX('Growth A'!$C$2:$N$31,0,MATCH(MONTH
($D2),MONTH(GrowthA'!$C$1:$N$1),0)),0))

Formula (2) Array Entered - Retrieve Numeric Label for Max Value by Specific
Day & Month
Col "A" (A2:A438) = Full Date formatted with Custom Day Format "dd": 1 - 31

Col "B" (B5:B438) = Full Date formatted with Custom Month Format "mmm": Jan -
Dec
Col "C" - "AO" (C4:AO4) = Numeric Labels
Col "C" - "AO" (C5:AO438) = Data Numeric Values

=INDEX('GrowthB'!$C$4:$AO$4,MATCH(MAX(INDEX('Growt hB'!$C$5:$AO$438,MATCH(1,IF
(DAY('GrowthB'!$A$5:$A$438)=DAY($D6),IF(MONTH('Gro wthB'!$B$5:$B$438)=MONTH(
$D6),1)),0),0)),INDEX('GrowthB'!$C$5:$AO$438,MATCH (1,IF(DAY('GrowthB'!$A$5:$A
$438)=DAY($D6),IF(MONTH('GrowthB'!$B$5:$B$438)=MON TH($D6),1)),0),0),0))


Thanks
Sam

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200702/1

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),M ONTH(GrowthA!$C$1:$N$1)
,0)))

F2:

=COUNTIF(INDEX(GrowthA!$C$2:$N$31,0,MATCH(MONTH($D 2),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(Gro wthB!$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(DAY (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)=DAY ($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!

In article <6e2650942ec3a@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi All,

I very much appreciate all the help I have received with these formulae.

I would appreciate further assistance in adapting the following formulae to
Return across a Single
Row Multiple Numeric Labels that share the same Maximum value (duplicate
maximums).


Formula (1) Array Entered - Retrieve Numeric Label for Max Value by Specific
Month
Col "A" (A2:A31) = Numeric Labels
Col "C" - "N" (C1:N1) = Full Date formatted with Custom Month Format "mmm":
Jan - Dec
Col "C" - "N" (C2:N31) = Data Numeric Values

=INDEX(GrowthA'!$A$2:$A$31,MATCH(MAX(INDEX('Growth A'!$C$2:$N$31,0,MATCH(MONTH
($D2),MONTH(GrowthA'!$C$1:$N$1),0))),INDEX('Growth A'!$C$2:$N$31,0,MATCH(MONTH
($D2),MONTH(GrowthA'!$C$1:$N$1),0)),0))

Formula (2) Array Entered - Retrieve Numeric Label for Max Value by Specific
Day & Month
Col "A" (A2:A438) = Full Date formatted with Custom Day Format "dd": 1 - 31

Col "B" (B5:B438) = Full Date formatted with Custom Month Format "mmm": Jan -
Dec
Col "C" - "AO" (C4:AO4) = Numeric Labels
Col "C" - "AO" (C5:AO438) = Data Numeric Values

=INDEX('GrowthB'!$C$4:$AO$4,MATCH(MAX(INDEX('Growt hB'!$C$5:$AO$438,MATCH(1,IF
(DAY('GrowthB'!$A$5:$A$438)=DAY($D6),IF(MONTH('Gro wthB'!$B$5:$B$438)=MONTH(
$D6),1)),0),0)),INDEX('GrowthB'!$C$5:$AO$438,MATCH (1,IF(DAY('GrowthB'!$A$5:$A
$438)=DAY($D6),IF(MONTH('GrowthB'!$B$5:$B$438)=MON TH($D6),1)),0),0),0))


Thanks
Sam

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