Excel: Find top ten values 2

[krets]

Would there be a way to use an array formula to pull the top ten values out of a list of 400 or so without using the sort feature?

 

[BlueHorizon]

Krets,

The AutoFilter feature (Data/Filter/AutoFilter) has a Top 10 component. Just click the arrow on the appropriate column and select Top 10. The rest is self-explanatory.

HTH,

Best,
Blue Horizon

 

[krets]

Yes, I realize Excel has those features built in but I am building this spreadsheet to feed into Xcelsius for a dashboard and a formula would be helpful.

 

[AnotherHiggins]

Define "Top 10".

My first thought was the highest 10 values.

Blue Horizon is probably right in his assumption that you mean 10 most frequently occurring.

If, by chance, you are looking for the highest 10, then you could use
[tab]=large(A:A,row())
(Not an array formula)
Copy down 10 rows to get the top 10.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[krets]

That sounds like it might work but what they would like to do is pull the top ten performers out of a list of 400 or so. So you'll have:

AgentName | RxProcessed
-----------------------
|
|

Etc. I'm simplifying of course but that's the basic idea. They would like to have a top ten list auto-generated for use in the aforementioned Xcelsius dashboard.

 

[AnotherHiggins]

I posted before seeing your response to Blue. Having read that, I assumed that I was on the wrong track. But now you say, "That sounds like it might work".

Are you talking to me or Blue?

I'm still not clear on what you want!

What will the data in the table look like?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[krets]

Sorry, I was talking to you AH.

The data will look like:

AgentName | RxProcessed
-----------------------
agent_na | 456
smith_bg | 123
jones_sm | 324

And etc. for 400+ agents.

 

[AnotherHiggins]

So you want the NAME returned, not the number? (Or in addition to the number)

To get the name you can combine the INDEX and MATCH functions, using the LARGE function within the MATCH.

It will look like this:
[tab][COLOR=blue white]=INDEX($A$2:$A$10, MATCH(LARGE($B$2:$B$10, ROW()), $B$2:$B$10, 0), 1)[/color]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[mintjulep]

How about using the RANK() function? Something like:

=if(rank(B2,B2:B401)<10,A2,"")

 

[PeterMoran]

Hi,

This more or less repeats what John has provided except that I have provided both Name and Number separately.

With the heading and then 10 names and numbers in cols A and B, the Numbers can be obtained with the formula:

=LARGE($B$2:$B$12,1)

where you increment the "1" for the value you want.

Then to get the Name use the following formula:

=INDEX($A$2:$A$12,MATCH(B16,Sheet1!$B$2:$B$12,FALSE))

where B16 is the location of the Number to be used.

Good Luck!

Peter Moran

 

[krets]

So what happens with the MATCH function if two agents produce the same number of scripts?

 

[krets]

PeterMoran's suggestion works almost perfectly. The only trouble now is that if two technicians have produced the same number of scripts, it lists the name of the first it finds in the range twice.

 

[Rofeu]

Hi,

I'm thinking that your best option would be a PivotTable.

use AgentName as rowfield and RxProcessed as datafield.

Then on AgentName set the Top10 option using field RxProcessed.

Cheers,

Roel

 

[krets]

I thought about that as well but I would really like to try to do this with formulas if at all possible. I may end up resorting to a PT, we'll see.

 

[krets]

I was thinking about this one and thought of something but I don't know if it is possible.

Could it look at the range to find the top ten numbers then pull the corresponding username?

For instance:

AgentName | RxProcessed
-----------------------
agent_na | 456
smith_bg | 123
jones_sm | 324

Is there a way to look at that range and see that 456 is the largest number but report the user name instead of the number? If I could do that I could figure it out.

 

[Gavona]

I think you could use the Large() function as criteria for advanced filter (to new location, unique values) or maybe a query to return the info you want.

Use named ranges "database", "criteria" and "extract" and the advanced filter dialogue will automatically fill with those ranges


Gavin

 

[PeterMoran]

Hi,

I see your problem with duplicates and it is a real problem with Excel to find the second entry when there are duplicates in a table.

One solution to duplicates would be to set up a filter on the Numbers and when you see that there are duplicate numbers in the results you could use the filter to see the details related to the duplicate entries.

On a different note why not just sort the list in descending sequence by number? If you don't want to resequence the list itself then copy the list to a new sheet and sort there. Then, of course, your required details are at the top of the list.

Good Luck!

Peter Moran

 

[yogia]

Ki Krets:

How about posting some sample data including some duplicates along with your expected result(s) -- so we can see what exactly you are working with and what you want to accomplish!

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[GlennUK]

Have a column of unique rankings, like this:

=RANK(B2,$B$2:$B$401)+COUNTIF($B$2:$B2,B2)-1

entered in cell C2, and copied down to row 401, and then you can use a simple formula to pick out item 1, then 2 then 3 etc.
Example of picking formulae:

=INDEX($A$2:$A$401,MATCH(1,$C$2:$C$401,0))
=INDEX($A$2:$A$401,MATCH(ROW()-1,$C$2:$C$401,0))

where MATCH(1 is picking the highest item, and the 1 can be replaced by any number from 1 to 10 to get your top 10 items. Or use the ROW()-1 function in formulae that are entered in rows 2-11, to automatically generate the item numbers 1 to 10.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Rofeu]

Hi,

how about this:

Col E has the AgentNames
Col F has the numbers

Use this formula to get the Top10 numbers (Col B in my example):

=LARGE($F$1:$F$16,ROW())

Use this array-formula to get the Names associated with the numbers (Col A in my example):

{=OFFSET($E$1,LARGE(--($F$1:$F$16=B5)*ROW($F$1:$F$16),COUNTIF($B$1:B1,B1))-1,0)}

It works for duplicates, it only reverses the order in which is encounters them, i.e.:

A 1
B 2
C 1

will result in:

B 2
C 1
A 1





Cheers,

Roel