|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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). |
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 |
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 |
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 |
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 |
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). |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 01:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com