find top 25 codes

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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.
 
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
Click to expand...

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
 
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!
 
Right! That combined with Ron's suggestion to use dynamic names created an
elegant solution.
 
Ron,

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

Thanks for the idea.
 
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)
 
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 formul
across and down.

Hope this helps
 
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 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 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.
 
Myrna said:
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!

[...]
Click to expand...

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)
 
Domenic,

You're right. It handles ties. I don't understand the array formula in E1.
It seems to always evaluate to zero.
 
Will that work when the formula is copied down? In the 2nd row, it becomes an
array: $1:2


Myrna said:
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!

[...]
Click to expand...

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).
Click to expand...
 
JBoulton said:
Domenic,

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

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
 
JBoulton said:
Domenic,

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

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
 
Aladin said:
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.
Click to expand...

Hi Aladin!

Yes, I noticed your new formula. Interesting! :
 
I'm betting you read it as: Row($1:1) and not RowS($1:1)?


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

Myrna said:
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!

[...]
Click to expand...

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).
Click to expand...
Click to expand...
 
Exactly. I missed that "S".

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


Myrna said:
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).
Click to expand...
Click to expand...
Click to expand...
 
Back
Top