lookup for top 10 values

S

swendingo

Board Regular
Joined
Sep 11, 2003
Messages
90
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?
 
Sort by date Sort by votes

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Aladin Akyurek,
Unless I am totally missing something, I have the forumulas exactly as you have used. It is actually working in most of the cells, just not for two names. Below is a link to part of the workbook. You will see on the left numbers are generated from other pages. On the right is where I would like the top 10 to be listed. From the top 10, graphs are generated on another tab. I really appreciate you looking at this to let me know what in the world is wrong.

https://www.dropbox.com/s/a1m3fw0k4jx1ay0/DropBox.xlsx

Thanks again!
Kim
 
Upvote 0
kmacklin said:
Aladin Akyurek,
Unless I am totally missing something, I have the forumulas exactly as you have used. It is actually working in most of the cells, just not for two names. Below is a link to part of the workbook. You will see on the left numbers are generated from other pages. On the right is where I would like the top 10 to be listed. From the top 10, graphs are generated on another tab. I really appreciate you looking at this to let me know what in the world is wrong.

https://www.dropbox.com/s/a1m3fw0k4jx1ay0/DropBox.xlsx

Thanks again!
Kim
Click to expand...

1) Calculation must be set to automatic.

2) The ranges must be equally sized. (The named range does not agree with the rest.)

3) The formulas should be:

BE5: 10

BE6, just enter:
Rich (BB code): 
=COUNTIF($BA$4:$BA$144,">="&LARGE($BA$4:$BA$144,BE5))
BE9, just enter and copy down:
Rich (BB code): 
=IF(ROWS(BE$9:BE9)<=$BE$6,LARGE($BA$4:$BA$144,ROWS(BE$9:BE9)),"")
BF9, control+shift+enter, not just enter, and copy down:
Rich (BB code): 
=IF(N(BE9),INDEX(D$4:D$144,
  SMALL(IF(BA$4:BA$144=BE9,ROW(BA$4:BA$144)-ROW(BA$4)+1),
   COUNTIF(BE$9:BE9,BE9))),"")
 
Upvote 0
I apologize for replying to such an old thread but it is the most relevant thread I could find relating to my question.

I have gotten the ranking formula to work very well in multiple worksheets. However, I want to take it to another level and exclude two specific values from the ranking (i.e. 0% and 100%). I never really understood how to use the NOT function correctly and would appreciate some assistance.

Thank you for your time.

Best regards,
K....
 
Upvote 0
KandaceX said:
I apologize for replying to such an old thread but it is the most relevant thread I could find relating to my question.

I have gotten the ranking formula to work very well in multiple worksheets. However, I want to take it to another level and exclude two specific values from the ranking (i.e. 0% and 100%). I never really understood how to use the NOT function correctly and would appreciate some assistance.

Thank you for your time.

Best regards,
K....
Click to expand...

Could you post a scaled-down sample and expected results?
 
Upvote 0
Hello and thanks for a response. I have read the question I posted a while ago but no longer use that spreadsheet so the question isn't applicable any longer.
Thanks anyay!
 
Upvote 0
Yes. How do I add an image without directing it to a web address?

The general idea is I only want a top ten and bottom ten list from about 25,000 records. I can get the top ten and bottom ten list but I have a significant amount of records that are 100% and 0% that I want to exclude from the ranking. I am unsure how to use the formulas/arrays to do it.


Company Name
Margin
Top Ten Margins
Bottom Ten Margins
COMPANY 1
100.3%
COMPANY 9
70.4%
COMPANY 24
0.6%
COMPANY 2
100.0%
COMPANY 10
59.1%
COMPANY 23
4.0%
COMPANY 3
100.0%
COMPANY 11
57.4%
COMPANY 22
8.8%
COMPANY 4
100.0%
COMPANY 12
57.2%
COMPANY 21
12.5%
COMPANY 5
100.0%
COMPANY 13
55.9%
COMPANY 20
32.0%
COMPANY 6
100.0%
COMPANY 14
55.8%
COMPANY 19
32.8%
COMPANY 7
100.0%
COMPANY 15
36.4%
COMPANY 18
34.0%
COMPANY 8
100.0%
COMPANY 16
36.2%
COMPANY 17
34.3%
COMPANY 9
70.4%
COMPANY 17
34.3%
COMPANY 16
36.2%
COMPANY 10
59.1%
COMPANY 18
34.0%
COMPANY 15
36.4%
COMPANY 11
57.4%
COMPANY 12
57.2%
COMPANY 13
55.9%
COMPANY 14
55.8%
COMPANY 15
36.4%
COMPANY 16
36.2%
COMPANY 17
34.3%
COMPANY 18
34.0%
COMPANY 19
32.8%
COMPANY 20
32.0%
COMPANY 21
12.5%
COMPANY 22
8.8%
COMPANY 23
4.0%
COMPANY 24
0.6%
COMPANY 25
0.0%
COMPANY 26
0.0%

<TBODY>
</TBODY>
 
Upvote 0
KandaceX said:
Yes. How do I add an image without directing it to a web address?

The general idea is I only want a top ten and bottom ten list from about 25,000 records. I can get the top ten and bottom ten list but I have a significant amount of records that are 100% and 0% that I want to exclude from the ranking. I am unsure how to use the formulas/arrays to do it.
[...]
Click to expand...

Thanks for the sample. Below is the data patially shown to spare space.

Company Name
Margin
10
COMPANY 1
100.30%
10
10
COMPANY 2
100.00%
Top Margins
Bottom Margins
COMPANY 3
100.00%
COMPANY 9
70.40%
COMPANY 24
0.60%
COMPANY 4
100.00%
COMPANY 10
59.10%
COMPANY 23
4.00%
COMPANY 5
100.00%
COMPANY 11
57.40%
COMPANY 22
8.80%
COMPANY 6
100.00%
COMPANY 12
57.20%
COMPANY 21
12.50%
COMPANY 7
100.00%
COMPANY 13
55.90%
COMPANY 20
32.00%
COMPANY 8
100.00%
COMPANY 14
55.80%
COMPANY 19
32.80%
COMPANY 9
70.40%
COMPANY 15
36.40%
COMPANY 18
34.00%
COMPANY 10
59.10%
COMPANY 16
36.20%
COMPANY 17
34.30%
COMPANY 11
57.40%
COMPANY 17
34.30%
COMPANY 16
36.20%
COMPANY 12
57.20%
COMPANY 18
34.00%
COMPANY 15
36.40%
COMPANY 13
55.90%
COMPANY 14
55.80%
COMPANY 15
36.40%
COMPANY 16
36.20%

<TBODY>
</TBODY>

G1: 10

E2, control+shift+enter (CSE), not just enter:
Rich (BB code): 
=SUM(IF(IF(($B$2:$B$27>0)*($B$2:$B$27<1),$B$2:$B$27,-9.9999999999999E+307)
  >=LARGE(IF($B$2:$B$27>0,IF($B$2:$B$27 < 1,$B$2:$B$27)),G1),1))
This formula adjusts Top 10 value in G1.

E4, CSE and copy down:
Rich (BB code): 
=IF($F4="","",INDEX($A$2:$A$27,
  SMALL(IF($B$2:$B$27=$F4,ROW($A$2:$A$27)-ROW($A$2)+1),
   COUNTIF($F$4:F4,F4))))

F4, CSE and copy down:
Rich (BB code): 
=IF(ROWS($F$4:F4)<=$E$2,LARGE(IF($B$2:$B$27>0,
  IF($B$2:$B$27 < 1,$B$2:$B$27)),ROWS($F$4:F4)),"")

H2, CSE:
Rich (BB code): 
=SUM(IF(IF(($B$2:$B$27>0)*($B$2:$B$27<1),$B$2:$B$27,9.9999999999999E+307)
  <=SMALL(IF($B$2:$B$27>0,IF($B$2:$B$27 < 1,$B$2:$B$27)),G1),1))

H4, CSE and copy down:
Rich (BB code): 
=IF($I4="","",INDEX($A$2:$A$27,
  SMALL(IF($B$2:$B$27=$I4,ROW($A$2:$A$27)-ROW($A$2)+1),
   COUNTIF($I$4:I4,I4))))

I4, CSE and copy down:
Rich (BB code): 
=IF(ROWS($I$4:I4)<=$H$2,SMALL(IF($B$2:$B$27>0,
  IF($B$2:$B$27 < 1,$B$2:$B$27)),ROWS($I$4:I4)),"")
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Shared workbook freezing when saving, but only for some.
Replies
0
Views
137
SingularitysPoint
S
O
  • Solved
unique, sort, top 5
Replies
6
Views
179
Fluff
Fluff
G
  • Question
List TOP 10 occurrences by Week/Month/YTD
Replies
0
Views
149
gldurand
G
megera716
  • Solved
Calculate maximum of 3 amounts for daily pay but I have the same date on multiple rows
Replies
4
Views
178
megera716
megera716
J
  • Question
Marking Top 10 in Criteria List
Replies
1
Views
117
Jezzzza
J

Forum statistics

Threads
1,216,091
Messages
6,128,779
Members
449,468
Latest member
AGreen17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top