lookup for top 10 values

[swendingo]

I have a table that has 1000 people who have a duration of time associated with them. I need to get the names and times of the top 10 people. I would prefer to use formulas so that I can just make a template that I can paste my data into.

anyone one have any ideas?

 

[excel?]

Will give it a try - Thanks you very much

 

[vane0326]

Assuming your data starts in column A input formula in cell B2 and copied down.


=INDEX($A$2:$A$20,MATCH(LARGE(ABS($A$2:$A$20),ROWS(A$2:A2)),ABS($A$2:$A$20),0))

It's an-array must hold down:

Ctrl,Shift,Enter

Hope it helps!

 

[Aladin Akyurek]

Great Info - was just about to start working on method of gathering Top 5 numbers so will begin here.

Question: Is there a way to look at both Positive & Negative amounts and pull Top 5 Largest Numbers?

Example:

10 Numbers [ 25,000; 15,000; 12,000; 10,000; 8,000; 4,000; -6,000; -11,000; -17,000; -32,000 ]

Top 5: -32,000; 25,000; -17,000; 15,000; 12,000

Thanks

This requires a different approach...

Book1
	A	B	C	D
1			5
2	Score		Top Score(s)
3	25000		-32000
4	15000		25000
5	12000		-17000
6	10000		15000
7	8000		12000
8	6000
9	-11000
10	-17000
11	-32000
12
Sheet1

C1: 5

C3:

=IF(ROWS($C$3:C3)<=$C$1,INDEX($A$3:$A$11,MAX(IF(ABS($A$3:$A$11)=LARGE(ABS($A$3:$A$11),ROWS($C$3:C3)),ROW($A$3:$A$11)-ROW($A$3)+1))),"")

which is confirmed with control+shift+enter (not just with enter) then copied down.

 

[LilStevie]

Any luck with my problem?

Mr. Aladin posted this great method for creating a top 10 list. I am attempting to adopt this to a spreadsheet however I need excel to only add a name to the top ten if the value in another cell is equal to a text string.

Still a newbie so any help would be greatly appreciated,

Go Bucks..

 

[Aladin Akyurek]

Re: Can't get this to work...

Mr. Aladin posted this great method for creating a top 10 list. I am attempting to adopt this to a spreadsheet however I need excel to only add a name to the top ten if the value in another cell is equal to a text string.

Still a newbie so any help would be greatly appreciated,

Go Bucks..

Conditional Top 10 List LilStevie.xls
	A	B	C	D	E	F
1					x
2					3
3					5
4	Name	Score	Text		Top Score(s)	Top Performer(s)
5	dawn	75	x		90	brian
6	damon	85	y		90	jon
7	dan	70	x		75	dawn
8	brian	90	x		75	stevie
9	christine	85	y		75	matt
10	ian	80	y
11	jon	90	x
12	stevie	75	x
13	fran	60	x
14	matt	75	x
15
Data

E1: x

which houses the string condition.

E2: 3

which houses the desired size of the Top N list.

E3:

=SUM(IF(C5:C14=E1,IF(B5:B14>=LARGE(IF(C5:C14=E1,B5:B14,""),E2),1,0)))

which must be confirmed with control+shift+enter (not just with enter).

This establishes the factual size of the Top N list.

E5:

=IF(ROWS(E$5:E5)<=$E$3,LARGE(IF($C$5:$C$14=$E$1,$B$5:$B$14),ROWS(E$5:E5)),"")

which must be confirmed with control+shift+enter (not just with enter) the copied down.

This determines the actual Top N scores.

F5:

=IF(N(E5),INDEX(A$5:A$14,SMALL(IF(B$5:B$14=E5,ROW(B$5:B$14)-ROW(B$5)+1),COUNTIF(E$5:E5,E5))),"")

which must be confirmed with control+shift+enter (not just with enter) the copied down.

This determines the actual names associated wit the Top N scores.

 

[LilStevie]

Beautiful...

Thanks for your help, I was headed in the correct direction just couldn't get the LARGE IF to work correctly...

You are a life saver.

 

[mitchv3]

Sorry everyone to reply to a very old post, but I'm having troubles with the solution.

I recreated Aladin's example, then added different names but repeated the scores.

If I sort the data ways, I get different "top scores". Why would this be?


Thanks,
Mitch

 

[kmacklin]

I know this is a very old thread but I have been looking for ways to do this for a while. I was able to Aladin's suggestion and it works for creating a top 10 except for the return of the names for position 3 and 5. I have verified that there are no duplicates, special characters, etc. All I recieve is a #REF! message. Any ideas? Thanks!

 

[Aladin Akyurek]

I know this is a very old thread but I have been looking for ways to do this for a while. I was able to Aladin's suggestion and it works for creating a top 10 except for the return of the names for position 3 and 5. I have verified that there are no duplicates, special characters, etc. All I recieve is a #REF! message. Any ideas? Thanks!

Which post are you referring to: Post #5, #11, or #15?

 

[kmacklin]

Post #5.