Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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 *** "TUNGANA KURMA RAJU" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
"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 . "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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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%),mar y(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" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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 "TUNGANA KURMA RAJU" wrote in message ... "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 . "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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
=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) "TUNGANA KURMA RAJU" wrote in message ... "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 . "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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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" wrote: =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) "TUNGANA KURMA RAJU" wrote in message ... "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 . "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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
Well I got the 3 correct results, top and bottom
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "TUNGANA KURMA RAJU" wrote in message ... 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" wrote: =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) "TUNGANA KURMA RAJU" wrote in message ... "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 . "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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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:D$10)*100/(D$2:D$10)=LARGE((C$2:C$10-D$2:D$10)*100/(D$2:D$10),3))),INDEX(A$2:A$10,MATCH(LARGE(((C$2:C $10-D$2:D$10)*100/(D$2:D$10))-ROW(D$2:D$10)/10^10,ROWS($1:1)),((C$2:C$10-D$2:D$10)*100/(D$2:D$10))-ROW(D$2:D$10)/10^10,0)),"") To get top 3 losers I have modified the other formula accordingly. Thank you so much for your help. "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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
To avoid print truncation:
=IF(ROWS($1:1)<=SUMPRODUCT(--((C$2:C$10-D$2:D$10) *100/(D$2:D$10)=LARGE((C$2:C$10-D$2:D$10)*100/ (D$2:D$10),3))),INDEX(A$2:A$10,MATCH(LARGE (((C$2:C$10-D$2:D$10)*100/(D$2:D$10))-ROW(D$2:D$10) /10^10,ROWS($1:1)),((C$2:C$10-D$2:D$10)*100/ (D$2:D$10))-ROW(D$2:D$10)/10^10,0)),"") "TUNGANA KURMA RAJU" wrote: 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:D$10) *100/(D$2:D$10)=LARGE((C$2:C$10-D$2:D$10)*100/ (D$2:D$10),3))),INDEX(A$2:A$10,MATCH(LARGE (((C$2:C$10-D$2:D$10)*100/(D$2:D$10))-ROW(D$2:D$10) /10^10,ROWS($1:1)),((C$2:C$10-D$2:D$10)*100/ (D$2:D$10))-ROW(D$2:D$10)/10^10,0)),"") To get top 3 losers I have modified the other formula accordingly. Thank you so much for your help. "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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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) "TUNGANA KURMA RAJU" wrote in message ... To avoid print truncation: =IF(ROWS($1:1)<=SUMPRODUCT(--((C$2:C$10-D$2:D$10) *100/(D$2:D$10)=LARGE((C$2:C$10-D$2:D$10)*100/ (D$2:D$10),3))),INDEX(A$2:A$10,MATCH(LARGE (((C$2:C$10-D$2:D$10)*100/(D$2:D$10))-ROW(D$2:D$10) /10^10,ROWS($1:1)),((C$2:C$10-D$2:D$10)*100/ (D$2:D$10))-ROW(D$2:D$10)/10^10,0)),"") "TUNGANA KURMA RAJU" wrote: 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:D$10) *100/(D$2:D$10)=LARGE((C$2:C$10-D$2:D$10)*100/ (D$2:D$10),3))),INDEX(A$2:A$10,MATCH(LARGE (((C$2:C$10-D$2:D$10)*100/(D$2:D$10))-ROW(D$2:D$10) /10^10,ROWS($1:1)),((C$2:C$10-D$2:D$10)*100/ (D$2:D$10))-ROW(D$2:D$10)/10^10,0)),"") To get top 3 losers I have modified the other formula accordingly. Thank you so much for your help. "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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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 "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 |
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
rank based array function
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rank Function | Excel Discussion (Misc queries) | |||
numerical integration | Excel Discussion (Misc queries) | |||
defintion of array function | Excel Discussion (Misc queries) | |||
Array | Excel Worksheet Functions | |||
Display the max, then the next down, then the next down, etc. | Excel Discussion (Misc queries) |