find top 25 codes

[JBoulton]

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

 

[Frank Kabel]

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

 

[Myrna Larson]

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.

 

[JBoulton]

Myrna and Frank,

FANTASTIC! Thanks for the lesson.

 

[Ron Rosenfeld]

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

 

[Myrna Larson]

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!

 

[JBoulton]

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

 

[JBoulton]

Ron,

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

Thanks for the idea.

 

[Peo Sjoblom]

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)

 

[Domenic]

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,D2100,0)),D2100))-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 formul
across and down.

Hope this helps

 

[Aladin Akyurek]

What follows constructs a Top N list.

Let A3:C11 house the following sample:

{"Code","Value1","Value2";1,100,1000;2,125,999;3,110,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: Her
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),B4)-1

Select A311.
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 calculata
automatically without adjusting any formulas or copying them dow
manually. This List feature is just great: It solves the formul
copying problem of the formula systems.

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

 

[Aladin Akyurek]

But instead of ROW()-ROW($D$2)+1, which is dependent on the address o
the
cell with the formula, use Frank's formula where he just write
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 th
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 th
cursor is in the formula)

 

[JBoulton]

Domenic,

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

 

[Myrna Larson]

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

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]

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

[...]

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

 

[Aladin Akyurek]

Domenic,

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

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

 

[Domenic]

Domenic,

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

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

 

[Domenic]

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

Hi Aladin!

Yes, I noticed your new formula. Interesting! :

 

[Dave Peterson]

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

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

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

 

[Myrna Larson]

Exactly. I missed that "S".

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

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

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