Ooops!

Results of Formula be in F2:F4=(top 3 losers)=lucy,george,ibrahim
Think it should be george, ibrahim, lucy.

Think it should be george, ibrahim, and murty

Biff

"Biff" wrote in message
...
Results of formula be in E2:E4=(top 3 gainers)=kent,joe,beck

What about lara?

Difference:

kent = 30
joe = 10
beck = 10
lara = 10

That's the problem with top/bottom n lists! Ties!

Results of Formula be in F2:F4=(top 3 losers)=lucy,george,ibrahim

Think it should be george, ibrahim, lucy.

The most robust way to do this requires helper cells:

http://www.excelforum.com/showthread.php?t=333697

However, these formulas (don't need helpers) **might** suit your needs:

For the top gainers (includes lara):

Array entered:

=IF(ROWS($1:1)<=SUMPRODUCT(--(C$2:C$10-D$2:D$10=LARGE(C$2:C$10-D$2:D$10,3))),INDEX(A$2:A$10,MATCH(LARGE(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)),"")

For the "top" losers:

=IF(ROWS($1:1)<=SUMPRODUCT(--(C$2:C$10-D$2:D$10<=SMALL(C$2:C$10-D$2:D$10,3))),INDEX(A$2:A$10,MATCH(SMALL(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)),"")

Copy down until you get blanks.

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
col A------ col B--------col C-------------col D-------------col E
names----job----------PresentSalary---EarlierSalary----
joe--------assistant---100----------------90
beck------secretary---110----------------100
george---manager----90-----------------100
kent------manager----210----------------180
lucy-------clerk--------23------------------26
mary------technician--500---------------498
murty-----clerk--------72-----------------76
lara-------chiefclerk---255----------------245
ibrahim---assistant----106---------------112

I am looking for a w/sheet array function that returns Top 3 gainers(
salary
gain percentage) from range A2:D10 without creating a helper column.I
tried
with abs,offset,large,and rank functions,but not succeeded.Also 3 Top
Losers
function.
Results of formula be in E2:E4=(top 3 gainers)=kent,joe,beck
Results of Formula be in F2:F4=(top 3 losers)=lucy,george,ibrahim