LARGE Worksheet function
Posted by Luke on January 28, 2002 12:20 AM
Hi I was wondering if you culd give me some advice on using the LARGE worksheet function. I am using it to find the top 15 values in a list. However it brings the same value up for different ranks e.g the same number for rank 3,4 and 5. However there are definately no equal values in my list. also when i encorporate the vlookup value the name returned for all three numbers is from the same row. ie. the value and name is appearing three times in my ranking. the data to which the large function is linked is live through the Bloomberg financial system but the same problem occurs even when i paste values. Any ideas? Much appreciated if you can help.
Posted by Aladin Akyurek on January 28, 2002 1:21 AM
Luke --
Try what follows.
I'll assume that A2:B7 houses the following sample data:
{"Name","Xval";
"AA",5;
"AB",10;
"AC",2;
"AD",10;
"AE",8}
Note that the data start at row 3 (Row 2 houses the labels).
In A1 enter: =MATCH(9.99999999999999E+307,B:B)-$E$2
In C3 enter: =RANK(B3,OFFSET($B$3,0,0,$A$1,1))+COUNTIF(B$3:B3,B3)-1
Give a double click on the little black square (lower right corner) of C3 in order to copy this formula as far as needed.
In E2 enter: =ROW()
In E3 enter: =IF(ROW()-$E$2<=3,INDEX(OFFSET($A$3,0,0,$A$1,1),MATCH(ROW()-$E$2,OFFSET($C$3,0,0,$A$1,1),0)),"")
Copy down this till 3 rows filled up with data. The number 3 means top 3 (in your case, it must be changed to 15).
In F3 enter: =IF(LEN(E3),INDEX(OFFSET($B$3,0,0,$A$1,1),MATCH(ROW()-$E$2,OFFSET($C$3,0,0,$A$1,1),0)),"")
Give a double click on the lower right corner of F3.
The system of formulas just described does exactly what you wanted to do with LARGE.
The same system can be used to get 3 (in your case 15) lowest values. The only change that is required is the one in C3:
=RANK(B3,OFFSET($B$3,0,0,$A$1,1),1)+COUNTIF(B$3:B3,B3)-1
Note that I just added a third parameter (whose value is 1) to the RANK function.
Aladin
=========
