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.

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.

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.