Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
JBoulton
 
Posts: n/a
Default find top 25 codes

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA

--
Jim
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

Hi in E12 enter the following formula
=INDEX($A$1:$A$100,MATCH(LARGE($B$1:$B$100,ROW(1:1 )),$B$1:$B$100,0))
and copy this down

Note: Does not work if you have ties in your list


--
Regards
Frank Kabel
Frankfurt, Germany


JBoulton wrote:
Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The
data changes constantly.

TIA

  #3   Report Post  
Myrna Larson
 
Posts: n/a
Default


Assuming codes in A2:A100, Value1 in B2:B100, the first formula in D2

=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-ROW($D$2)+1),$B$2:$B$100,0))

and copy down for 25 rows.

If you put the first formula in a cell other than D2, you must change the $D$2
to refer to that cell with the 1st formula.


On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA


  #4   Report Post  
JBoulton
 
Posts: n/a
Default

Myrna and Frank,

FANTASTIC! Thanks for the lesson.

"Myrna Larson" wrote:


Assuming codes in A2:A100, Value1 in B2:B100, the first formula in D2

=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-ROW($D$2)+1),$B$2:$B$100,0))

and copy down for 25 rows.

If you put the first formula in a cell other than D2, you must change the $D$2
to refer to that cell with the 1st formula.


On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA



  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA


You may use the LARGE worksheet function.

Assuming your table is in A1:Cn and you want the results in E2:F25,

Name the first two columns Code and Value1.

In E2:E26 enter the numbers 1-25

In F2 place the formula:

=INDEX(Code,MATCH(LARGE(Value1,E2),Value1,0))

Copy/Drag this down as far as needed.


--ron


  #6   Report Post  
Myrna Larson
 
Posts: n/a
Default

But instead of ROW()-ROW($D$2)+1, which is dependent on the address of the
cell with the formula, use Frank's formula where he just writes ROW(1:!)

I don't know why, but I always forget about that!



On Fri, 29 Oct 2004 14:30:03 -0700, "JBoulton"
wrote:

Myrna and Frank,

FANTASTIC! Thanks for the lesson.

"Myrna Larson" wrote:


Assuming codes in A2:A100, Value1 in B2:B100, the first formula in D2


=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-ROW($D$2)+1),$B$2:$B$100,0))

and copy down for 25 rows.

If you put the first formula in a cell other than D2, you must change the

$D$2
to refer to that cell with the 1st formula.


On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA




  #7   Report Post  
JBoulton
 
Posts: n/a
Default

Right! That combined with Ron's suggestion to use dynamic names created an
elegant solution.

"Myrna Larson" wrote:

But instead of ROW()-ROW($D$2)+1, which is dependent on the address of the
cell with the formula, use Frank's formula where he just writes ROW(1:!)

I don't know why, but I always forget about that!



On Fri, 29 Oct 2004 14:30:03 -0700, "JBoulton"
wrote:

Myrna and Frank,

FANTASTIC! Thanks for the lesson.

"Myrna Larson" wrote:


Assuming codes in A2:A100, Value1 in B2:B100, the first formula in D2


=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-ROW($D$2)+1),$B$2:$B$100,0))

and copy down for 25 rows.

If you put the first formula in a cell other than D2, you must change the

$D$2
to refer to that cell with the 1st formula.


On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA




  #8   Report Post  
JBoulton
 
Posts: n/a
Default

Ron,

Following your suggestion, I set up dynamic names. Now I have a great
solution.

Thanks for the idea.

"Ron Rosenfeld" wrote:

On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The data
changes constantly.

TIA


You may use the LARGE worksheet function.

Assuming your table is in A1:Cn and you want the results in E2:F25,

Name the first two columns Code and Value1.

In E2:E26 enter the numbers 1-25

In F2 place the formula:

=INDEX(Code,MATCH(LARGE(Value1,E2),Value1,0))

Copy/Drag this down as far as needed.


--ron

  #9   Report Post  
Peo Sjoblom
 
Posts: n/a
Default

I can tell you the reason you are using it,
it's because it is independent of row insertions above the formula,
so it is more stable

Frank's formula will return wrong result if you insert a row above the
formula
--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)



"Myrna Larson" wrote in message
...
But instead of ROW()-ROW($D$2)+1, which is dependent on the address of the
cell with the formula, use Frank's formula where he just writes ROW(1:!)

I don't know why, but I always forget about that!



On Fri, 29 Oct 2004 14:30:03 -0700, "JBoulton"
wrote:

Myrna and Frank,

FANTASTIC! Thanks for the lesson.

"Myrna Larson" wrote:


Assuming codes in A2:A100, Value1 in B2:B100, the first formula in D2


=INDEX($A$2:$A$100,MATCH(LARGE($B$2:$B$100,ROW()-ROW($D$2)+1),$B$2:$B$100,0))

and copy down for 25 rows.

If you put the first formula in a cell other than D2, you must change
the

$D$2
to refer to that cell with the 1st formula.


On Fri, 29 Oct 2004 13:51:01 -0700, "JBoulton"
wrote:

Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The
data
changes constantly.

TIA





  #10   Report Post  
Domenic
 
Posts: n/a
Default


Here's an approach that will take ties into consideration...

D2, copied down:

=RANK(B2,$B$2:$B$100)+COUNTIF($B$2:B2,B2)-1

E1:

=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(F1,D2:D100,0)) ,D2:D100))-F1

...entered using CONTROL+SHIFT+ENTER.

F1: contains your Top N parameter, in this case 25

G2, copied down:

=IF(ROW()-ROW(G$2)+1<=$E$1+$F$1,INDEX(A$2:A$100,MATCH(ROW()-ROW(G$2)+1,$D$2:$D$100,0)),"")

If you want to display the corresponding information, copy this formula
across and down.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740



  #11   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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.


JBoulton Wrote:
Hi, all.

My data looks like this:

Code Value1 Value2
1 100 1000
2 125 999
3 110 25

How can I list the codes for the 25 largest numbers in Value1? The
data
changes constantly.

TIA

--
Jim



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

  #12   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


Myrna Larson Wrote:
But instead of ROW()-ROW($D$2)+1, which is dependent on the address of
the
cell with the formula, use Frank's formula where he just writes
ROW(1:!)

I don't know why, but I always forget about that!

[...]



ROW(1:1) and similar idioms should never be used for it is one of the
sources of spreadsheet errors by giving way to non-robust formulas.

If ROW()-ROW($D$2)+1 looks too baroque, you could use

ROWS($1:1)

instead (which I somewhat dislike because of how it looks when the
cursor is in the formula).


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

  #13   Report Post  
JBoulton
 
Posts: n/a
Default

Domenic,

You're right. It handles ties. I don't understand the array formula in E1.
It seems to always evaluate to zero.

"Domenic" wrote:


Here's an approach that will take ties into consideration...

D2, copied down:

=RANK(B2,$B$2:$B$100)+COUNTIF($B$2:B2,B2)-1

E1:

=MAX(IF(B2:B100=INDEX(B2:B100,MATCH(F1,D2:D100,0)) ,D2:D100))-F1

...entered using CONTROL+SHIFT+ENTER.

F1: contains your Top N parameter, in this case 25

G2, copied down:

=IF(ROW()-ROW(G$2)+1<=$E$1+$F$1,INDEX(A$2:A$100,MATCH(ROW()-ROW(G$2)+1,$D$2:$D$100,0)),"")

If you want to display the corresponding information, copy this formula
across and down.

Hope this helps!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740


  #14   Report Post  
Myrna Larson
 
Posts: n/a
Default

Will that work when the formula is copied down? In the 2nd row, it becomes an
array: $1:2


On Fri, 29 Oct 2004 18:03:21 -0500, Aladin Akyurek
wrote:


Myrna Larson Wrote:
But instead of ROW()-ROW($D$2)+1, which is dependent on the address of
the
cell with the formula, use Frank's formula where he just writes
ROW(1:!)

I don't know why, but I always forget about that!

[...]



ROW(1:1) and similar idioms should never be used for it is one of the
sources of spreadsheet errors by giving way to non-robust formulas.

If ROW()-ROW($D$2)+1 looks too baroque, you could use

ROWS($1:1)

instead (which I somewhat dislike because of how it looks when the
cursor is in the formula).


  #15   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


Myrna Larson Wrote:
Will that work when the formula is copied down? In the 2nd row, it
becomes an
array: $1:2

[...]


Yes, it will. It's result is not array.


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



  #16   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


JBoulton Wrote:
Domenic,

You're right. It handles ties. I don't understand the array formula
in E1.
It seems to always evaluate to zero.
...


If correctly set up, it will calculate the ties of the Nth value. I
recently replaced it with an ordinary formula as shown in my reply.


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

  #17   Report Post  
Domenic
 
Posts: n/a
Default


JBoulton Wrote:
Domenic,

You're right. It handles ties. I don't understand the array formula
in E1.
It seems to always evaluate to zero.


If there is more than one value ranked 25, then all of those values
will be displayed in addition to the top 24. The formula in E1 helps
effect such a situation.


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

  #18   Report Post  
Domenic
 
Posts: n/a
Default


Aladin Akyurek Wrote:
If correctly set up, it will calculate the ties of the Nth value. I
recently replaced it with an ordinary formula as shown in my reply.


Hi Aladin!

Yes, I noticed your new formula. Interesting! :)


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

  #19   Report Post  
Dave Peterson
 
Posts: n/a
Default

I'm betting you read it as: Row($1:1) and not RowS($1:1)?


Myrna Larson wrote:

Will that work when the formula is copied down? In the 2nd row, it becomes an
array: $1:2

On Fri, 29 Oct 2004 18:03:21 -0500, Aladin Akyurek
wrote:


Myrna Larson Wrote:
But instead of ROW()-ROW($D$2)+1, which is dependent on the address of
the
cell with the formula, use Frank's formula where he just writes
ROW(1:!)

I don't know why, but I always forget about that!

[...]



ROW(1:1) and similar idioms should never be used for it is one of the
sources of spreadsheet errors by giving way to non-robust formulas.

If ROW()-ROW($D$2)+1 looks too baroque, you could use

ROWS($1:1)

instead (which I somewhat dislike because of how it looks when the
cursor is in the formula).


--

Dave Peterson

  #20   Report Post  
Myrna Larson
 
Posts: n/a
Default

Exactly. I missed that "S".

On Fri, 29 Oct 2004 20:04:10 -0500, Dave Peterson wrote:

I'm betting you read it as: Row($1:1) and not RowS($1:1)?


Myrna Larson wrote:

Will that work when the formula is copied down? In the 2nd row, it becomes

an
array: $1:2

On Fri, 29 Oct 2004 18:03:21 -0500, Aladin Akyurek
wrote:


Myrna Larson Wrote:
But instead of ROW()-ROW($D$2)+1, which is dependent on the address of
the
cell with the formula, use Frank's formula where he just writes
ROW(1:!)

I don't know why, but I always forget about that!

[...]



ROW(1:1) and similar idioms should never be used for it is one of the
sources of spreadsheet errors by giving way to non-robust formulas.

If ROW()-ROW($D$2)+1 looks too baroque, you could use

ROWS($1:1)

instead (which I somewhat dislike because of how it looks when the
cursor is in the formula).




  #21   Report Post  
Domenic
 
Posts: n/a
Default


I find that the results differ when using your old and new formulas for
the Top N list.

For example, if I enter 6 as the Top N value I get the following
results:

Old formula [MAX(IF(.....]

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

...which seems correct. The last value is included since it's tied
with the Top N value.

New formula [=COUNTIF(......]

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

...which doesn't seem correct. I don't understand why that last value
of 100 is included.

What am I missing?

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.



--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=273740

  #22   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


You're right. I should have thought more about it before deciding to
switch. Thanks for looking at it.

The OP should use in F2 the original:

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

which must be confirmed with control+shift+enter instead of just
enter.

instead of the flawed:

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

Domenic Wrote:
I find that the results differ when using your old and new formulas for
the Top N list.

For example, if I enter 6 as the Top N value I get the following
results:

Old formula [MAX(IF(.....]

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

...which seems correct. The last value is included since it's tied
with the Top N value.

New formula [=COUNTIF(......]

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

...which doesn't seem correct. I don't understand why that last value
of 100 is included.

What am I missing?



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

  #23   Report Post  
Aladin Akyurek
 
Posts: n/a
Default


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

  #24   Report Post  
JBoulton
 
Posts: n/a
Default

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I compare data in two worksheets to find matching cells? Gary Excel Discussion (Misc queries) 4 March 2nd 06 10:04 PM
Printing zip codes that start with 0 ET13 Excel Discussion (Misc queries) 2 January 17th 05 01:13 AM
Printing zip codes that start with 0 ET13 Excel Discussion (Misc queries) 0 January 17th 05 12:47 AM
Excel has a "Find Next" command but no "Find Previous" command. Michael Fitzpatrick Excel Discussion (Misc queries) 2 January 11th 05 12:45 AM
Find & Replace results to display specified chosen fields samuel Excel Discussion (Misc queries) 1 December 28th 04 09:43 AM


All times are GMT +1. The time now is 03:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"