=LARGE Function Question

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

Guest

I need some help!!!
I have a list of physicians and corresponding surgical case per month that I
need to be able to manipulate...

Assume:
Tom 20
Will 22
Mike 30
Ed 22
John 22

The list is obviously much longer and there is data for each month.
I need to pull the top 5 for each month.
I tried to use
A7=LARGE(B1:B5,1) [30] B7=INDEX(A1:A5,(MATCH(A7,B1:B5,0)) [Mike]
A8=LARGE(B1:B5,2) [22] B8=INDEX(A1:A5,(MATCH(A8,B1:B5,0)) [Will]
A9=LARGE(B1:B5,3) [22] B9=INDEX(A1:A5,(MATCH(A9,B1:B5,0)) [Will]
etc..

But, when I have the same values (Will, Ed, and John = 22), I can only get
"Will."
Any ideas or suggestions?
THANKS!!!
Jim
 
hi,
the formula that you are using only select the first
occurance which is why you only get will.
Suggestion. why not add a helper column and fill it when
descending number 1 to whatever.
then sort the data assending by the column that the top 5
would be in. your top 5 would be at the top. you could
then resort descending by the helper column to put
everything back the way is was.
yes, a bit machanical but if you only do it once a month,
it shouldn't be that much of a hassel.

regards
Frank
 
This may not help, either, but if you applied Data|Filter|autofilter, you could
filter on that quantity of cases column and do "Top 10" (it's adjustable to show
the top 5).

And ties will appear in that filtered list.



TN.Jim said:
I need some help!!!
I have a list of physicians and corresponding surgical case per month that I
need to be able to manipulate...

Assume:
Tom 20
Will 22
Mike 30
Ed 22
John 22

The list is obviously much longer and there is data for each month.
I need to pull the top 5 for each month.
I tried to use
A7=LARGE(B1:B5,1) [30] B7=INDEX(A1:A5,(MATCH(A7,B1:B5,0)) [Mike]
A8=LARGE(B1:B5,2) [22] B8=INDEX(A1:A5,(MATCH(A8,B1:B5,0)) [Will]
A9=LARGE(B1:B5,3) [22] B9=INDEX(A1:A5,(MATCH(A9,B1:B5,0)) [Will]
etc..

But, when I have the same values (Will, Ed, and John = 22), I can only get
"Will."
Any ideas or suggestions?
THANKS!!!
Jim
Click to expand...
 
Back
Top