Yes u are right your's formula is takencare of possible ties.I am having a
long list which has ties.It worked well.
To make my original post more complex can you give or modify the present
formula so that it returns top 3 gainers( salary gain percentage) job wise.Is
it possible?

"Biff" wrote:

Hi!

Ok......

Bob's formula does work, BTW.

The main difference in my approach is that I try to account for possible
ties. I usually avoid replying to requests for top/bottom n lists because
the concept to me is so ambiguous.

Bob provided a link to explain the use of the "--" in the Sumproduct
function.

The "^" is the math operator meaning "to the power of". So, 10^10 means 10
to the 10th power. This is used as the means of breaking ties.

SOME_VALUE-ROW(D$2:D$10)/10^10

For example, suppose we have this data:

.......A......B
1...Joe....10
2...Sue...10

We need a means of breaking the tie in order for the lookup portion of the
formula to work properly. Normally, you would use a helper cell but since
you specifically requested not to use any helpers we need a different
method. That's where -ROW(D$2:D$10)/10^10 comes in handy. Written for the
above example it would be: -ROW(B$1:B$2)/10^10

Here's how it works:

B1 - ROW(1) / 10 ^ 10 =
10 - 1 / 10,000,000,000 =
10 - 0.0000000001 =
9.9999999999

B2 - ROW(2) / 10 ^ 10 =
10 - 2 / 10,000,000,000 =
10 - 0.0000000002 =
9.9999999998

So, now we have broken the 10 - 10 tie. However, now that I understand that
you wanted to base this on the PERCENTAGE of change, ties may be very
unlikely (but still possible). So using a tiebreaker may just be adding
unnecessay complexity to the formula. But you're the only one that can
determine that, it's your file and data. You know what *YOU* need to do, we
can only offer our best "educated" guess! So, with that said, you could use
this formula that still breaks ties:

=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)),"")

Bob's formula is shorter but does not account for ties.

Biff

"TUNGANA KURMA RAJU" wrote in
message ...
Biff,with small modification in both the formulas ,I got correct results
as I
wanted.
You have used 'sumproduct', '--', '^' ... what these symbols and
sumproduct
do in my formula.Can you please explain.

"Biff" wrote:

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