Aladin,

Thanks for a very robust solution!

"Aladin Akyurek" wrote:


Please replace the formula in F2, which is flawed, with:

=MAX(IF(INDEX(B4:B11,MATCH(F1,D4:D11,0))=B4:B11,D4 :D11))-F1

which you need to confirm with control+shift+enter instead of just
enter.

Thanks to Domenic for keeping me to my original formula system.

Aladin Akyurek Wrote:
What follows constructs a Top N list.

Let A3:C11 house the following sample:

{"Code","Value1","Value2";1,100,1000;2,125,999;3,1 10,25;5,140,8;6,140,3;8,120,9;9,110,5;20,110,8}

In D3 enter: Rank

In D4 enter & copy down:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:B4,B4)-1

Enter the Top N parameter value in F1: 5 (in this example).

In F2 enter:

=COUNTIF(B4:B11,LARGE(B4:B11,F1))-1

This calculates the ties of Nth highest value.

In F3 enter: Top N

In F4 enter & copy down:

=IF(G4<"",INDEX($A$4:$A$11,MATCH(ROW()-ROW(F$4)+1,$D$4:$D$11,0)),"")

The ROW(F$4) anchors the formula to the first cell it's entered: Here
F4.

In G3 enter: Value1

In G4 enter & copy down:

=IF(ROW()-ROW($G$4)+1<=$F$1+$F$2,INDEX($B$4:$B$11,MATCH(ROW( )-ROW(G$4)+1,$D$4:$D$11,0)),"")

The results area will look like this:

{5,140;6,140;2,125;8,120;3,110;9,9;20,20}


If you are on Excel 2003, do the following:

Change the formula in D4 from:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:B4,B4)-1

to:

=RANK(B4,$B$4:$B$11)+COUNTIF($B$4:OFFSET(B4,0,0),B 4)-1

Select A3:D11.
Activate Data|List|Create List.
Check the "My list has headers" option.
Click OK.

Repeat the foregoing steps for F3:G10.

Whenever you add records to A:C, everything will be calculatad
automatically without adjusting any formulas or copying them down
manually. This List feature is just great: It solves the formula
copying problem of the formula systems.

A side note. It's surprising that the List functionality cannot cope
with the original formula in D4, a fact that forces us to introduce an
additional function call with the volatile OFFSET(). I'd urge Microsoft
to lift up this shortcoming of the otherwise very promising feature.


--
Aladin Akyurek
------------------------------------------------------------------------
Aladin Akyurek's Profile: http://www.excelforum.com/member.php...fo&userid=4165
View this thread: http://www.excelforum.com/showthread...hreadid=273740