rank based array function

[Guest]

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 A210 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

 

[Guest]

Try this ...

=INDIRECT("A"&MATCH(LARGE(C2:C10,1),C2:C10,0))&",
"&INDIRECT("A"&MATCH(LARGE(C2:C10,2),C2:C10,0))&",
"&INDIRECT("A"&MATCH(LARGE(C2:C10,3),C2:C10,0))

Assumed that your data is from C2:C10

*** Please do rate ***

 

[Biff]

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

For the "top" losers:

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

Copy down until you get blanks.

Biff

 

[Biff]

Ooops!

Think it should be george, ibrahim, lucy.

Think it should be george, ibrahim, and murty

Biff

 

[Guest]

"lara' is 4th top gainer.My requirement is 'top 3 gainers'.(lara's gain
percent is 4.08).
F2:F4= you are wrong.Top 3 losers are
lucy(-11.54%),george(-10.00%),ibrahim(-5.36%).Thank you Biff .

 

[Guest]

My results top 3 gainers & top 3 losers comparing their gain percentage and
lose percentage of their salaries change.
gainer in top orderwise are
kent(16.67%),joe(11.11%),beck(10%),lara(4.08%),mary(0.40%).
losers in top orderwise are
lucy(-11.54%),george(-10%),ibrahim(-5.36%),murty(-5.26%).
your formula is giving results 'lara' also who is 4th top gainer.

 

[Biff]

Oh, I was going by the net gain/loss NOT the %. Sorry!

Well, I don't have time to redo it tonight. It's 3:30 AM where I'm at. Maybe
tomorrow or maybe someone else will chime in.

Biff

 

[Bob Phillips]

=INDIRECT("A"&MATCH(LARGE(($C$2:$C$10-$D$2:$D$10)/$D$2:$D$10,ROW(A1)),($C$2:
$C$10-$D$2:$D$10)/$D$2:$D$10,0)+1)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

and copy down 2 rows.

For losers, change LARGE to SMALL

--
HTH

Bob Phillips

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

 

[Guest]

Mr.Bob, somewhere a bug is there.I got 1 result correct(kent),for second row
N/A,third row I got "joe".Actually, I asked for 3 top gainers.not two.Thanks.

 

[Bob Phillips]

Well I got the 3 correct results, top and bottom

--
HTH

Bob Phillips

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

 

[Guest]

Biff,taking cue from your earlier formula I have modified it and got the
correct results.
Array entered:
to get top 3 gainers
=IF(ROWS($1:1)<=SUMPRODUCT(--((C$2:C$10-D$2$10)*100/(D$2$10)>=LARGE((C$2:C$10-D$2$10)*100/(D$2$10),3))),INDEX(A$2:A$10,MATCH(LARGE(((C$2:C$10-D$2$10)*100/(D$2$10))-ROW(D$2$10)/10^10,ROWS($1:1)),((C$2:C$10-D$2$10)*100/(D$2$10))-ROW(D$2$10)/10^10,0)),"")
To get top 3 losers I have modified the other formula accordingly.
Thank you so much for your help.

 

[Guest]

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.

 

[Guest]

To avoid print truncation:
=IF(ROWS($1:1)<=SUMPRODUCT(--((C$2:C$10-D$2$10)
*100/(D$2$10)>=LARGE((C$2:C$10-D$2$10)*100/
(D$2$10),3))),INDEX(A$2:A$10,MATCH(LARGE
(((C$2:C$10-D$2$10)*100/(D$2$10))-ROW(D$2$10)
/10^10,ROWS($1:1)),((C$2:C$10-D$2$10)*100/
(D$2$10))-ROW(D$2$10)/10^10,0)),"")

 

[Bob Phillips]

See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation.

--
HTH

Bob Phillips

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

 

[Biff]

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

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

Biff

 

[Guest]

Thank you Biff,I am stuck up today with this formual.I practically used this
formula in one of my project which contains list range A2:A2600.When I wrote
this formula array entered to get top 10 gainers(percentage wise),#DIV/0
error is coming.I also used 2600^2600 in my formula.Is there any limitations
in using this formula

 

[Guest]

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?