include criteria to 'rank based array function'

[Guest]

Hi, Everybody.I got a robust formula from Mr.Biff and Mr.Bob against my post
"rank based array function'.
Can any body make it more robust by adding one more criteria i.e 'Job'
wise(B2:B10).In precise, I am looking for a w/sheet function that returns Top
3 gainers( salary increase percetage wise) by job wise,that means Top 3
managers,Top 3 clerks etc....who gained/lost most.
Since Mr.Biff used Sumproduct function with unary I presume one more
criteria can be added as an array to sumproduct function.

 

[Bob Phillips]

If only life were that simple

=IF(ROWS($1:1)<=SUMPRODUCT(--(C$2:C$10/D$2$10>=LARGE(IF($B$2:$B$10="manage
r",C$2:C$10/D$2$10,0),3)),--($B$2:$B$10="manager")),
INDEX(A$2:A$10,MATCH(LARGE((IF($B$2:$B$10="manager",C$2:C$10/D$2$10))-ROW(
D$2$10)/10^10,ROWS($1:1)),(C$2:C$10/D$2$10)-ROW(D$2$10)/10^10,0)),"")

still an array formula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thank you Bob,You saved me from sleepless night.

If only life were that simple

=IF(ROWS($1:1)<=SUMPRODUCT(--(C$2:C$10/D$2$10>=LARGE(IF($B$2:$B$10="manage
r",C$2:C$10/D$2$10,0),3)),--($B$2:$B$10="manager")),
INDEX(A$2:A$10,MATCH(LARGE((IF($B$2:$B$10="manager",C$2:C$10/D$2$10))-ROW(
D$2$10)/10^10,ROWS($1:1)),(C$2:C$10/D$2$10)-ROW(D$2$10)/10^10,0)),"")

still an array formula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)