include criteria to 'rank based array function'
 

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.

include criteria to 'rank based array function'
 

If only life were that simple

=IF(ROWS($1:1)<=SUMPRODUCT(--(C$2:C$10/D$2:D$10=LARGE(IF($B$2:$B$10="manage
r",C$2:C$10/D$2:D$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:D$10))-ROW(
D$2:D$10)/10^10,ROWS($1:1)),(C$2:C$10/D$2:D$10)-ROW(D$2:D$10)/10^10,0)),"")

still an array formula

--
HTH

Bob Phillips

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

"TUNGANA KURMA RAJU" wrote in
message ...
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.

include criteria to 'rank based array function'
 

Thank you Bob,You saved me from sleepless night.

"Bob Phillips" wrote:

If only life were that simple

=IF(ROWS($1:1)<=SUMPRODUCT(--(C$2:C$10/D$2:D$10=LARGE(IF($B$2:$B$10="manage
r",C$2:C$10/D$2:D$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:D$10))-ROW(
D$2:D$10)/10^10,ROWS($1:1)),(C$2:C$10/D$2:D$10)-ROW(D$2:D$10)/10^10,0)),"")

still an array formula

--
HTH

Bob Phillips

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

"TUNGANA KURMA RAJU" wrote in
message ...
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.