Ranking values in Excel

pjandshelly

Board Regular
Joined
Jan 25, 2017
Messages
61
I am working with a large data set. The values are included in two different columns. A lot of the values have very little value to the overall report impact. What I want to do is identify the top 10 items by percentages that have a number greater than 50 in my top 10 table separate from the main data set.

Service percentage Number of visits
Cardiology 15% 65
Medicine 16% 75
Surgery 17% 30

My final output would be:
Medicine
Cardiology

Surgery would not be included in the results because it only had 30 visits. Any help would be appreciated.
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Hi, here is an option you can try:

The formulas in E2 and F2 can be copied down 10 rows.


Excel 2013/2016
ABCDEF
1ServicepercentageNumber of visits%Service
2Cardiology15%6516%Medicine
3Medicine16%7515%Cardiology
4Surgery17%30
Sheet1
Cell Formulas
RangeFormula
E2=IFERROR(AGGREGATE(14,6,$B$2:$B$4/($C$2:$C$4>50),ROWS(E$2:E2)),"")
F2=IF(E2="","",INDEX($A$2:$A$4,AGGREGATE(14,6,(ROW($A$2:$A$4)-MIN(ROW($A$2:$A$4))+1)/(($B$2:$B$4=E2)*($C$2:$C$4>50)),COUNTIFS(E$2:E2,E2))))
 
Upvote 0
Hi, here is an option you can try:

The formulas in E2 and F2 can be copied down 10 rows.

Excel 2013/2016
ABCDEF
ServicepercentageNumber of visits%Service
Cardiology
MedicineCardiology
Surgery

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]15%[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]16%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Medicine[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]16%[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15%[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]17%[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1


[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=IFERROR(AGGREGATE(14,6,$B$2:$B$4/($C$2:$C$4>50),ROWS(E$2:E2)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IF(E2="","",INDEX($A$2:$A$4,AGGREGATE(14,6,(ROW($A$2:$A$4)-MIN(ROW($A$2:$A$4))+1)/(($B$2:$B$4=E2)*($C$2:$C$4>50)),COUNTIFS(E$2:E2,E2))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

That worked like a charm. I probably should have added this in the original email. My data is a rolling 12 month table. Is there an easier way to say if column a1 = Apr-19, then check the different columns for each. My current data set changes the amount of items on the list. So if I changed the month that it was looking for, then give the top 10 by percentage. So three criteria would be: = month in question, number of visits > 50, and top 10 services.
 
Upvote 0
Is there an easier way to say if column a1 = Apr-19

What is in your date column - is it an Excel date formatted to show only the month and year? If it is, is it always the first of the month? Or is it the literal text "Arp-19"?
 
Upvote 0
What is in your date column - is it an Excel date formatted to show only the month and year? If it is, is it always the first of the month? Or is it the literal text "Arp-19"?

It is a data set that I am copying from another database. I would always type the name of the month in the table prior to sending the report out. It is literally Apr-19, Mar-19. Their output seems to be the first three characters in the month and the last two of the year with dash between.
 
Upvote 0
It is a data set that I am copying from another database. I would always type the name of the month in the table prior to sending the report out. It is literally Apr-19, Mar-19. Their output seems to be the first three characters in the month and the last two of the year with dash between.

I need to edit that last post. It is a custom format and only pulling the month and year with Apr-19. A better solution would be if it could identify the latest month on the list and provide that output. So if the latest date was Mar-19, then it would search through all the data for only that data set.
 
Last edited:
Upvote 0
Ok - you can give this a try..


Excel 2013/2016
ABCDEFG
1ServicepercentageNumber of visitsDate%Service
2Cardiology20%6501-Apr-201925%other
3Medicine25%7501-Apr-201916%Medicine
4Surgery60%3001-Apr-201915%Cardiology
5Cardiology15%9901-May-2019
6Medicine16%7501-May-2019
7Surgery17%3001-May-2019
8other25%9901-May-2019
Sheet1
Cell Formulas
RangeFormula
F2=IFERROR(AGGREGATE(14,6,$B$2:$B$8/(($C$2:$C$8>50)*($D$2:$D$8=MAX($D$2:$D$8))),ROWS(F$2:F2)),"")
G2=IF(F2="","",INDEX($A$2:$A$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1)/(($B$2:$B$8=F2)*($C$2:$C$8>50)*($D$2:$D$8=MAX($D$2:$D$8))),COUNTIFS(F$2:F2,F2))))
 
Upvote 0
Ok - you can give this a try..

Excel 2013/2016
ABCDEFG
ServicepercentageNumber of visitsDate%Service
Cardiology
MedicineMedicine
SurgeryCardiology
Cardiology
Medicine
Surgery
other

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]20%[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]01-Apr-2019[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: right"]25%[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]other[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]25%[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]01-Apr-2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]16%[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]60%[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]01-Apr-2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"]15%[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]15%[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]01-May-2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]16%[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]01-May-2019[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]17%[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]01-May-2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]25%[/TD]
[TD="align: right"]99[/TD]
[TD="align: right"]01-May-2019[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IFERROR(AGGREGATE(14,6,$B$2:$B$8/(($C$2:$C$8>50)*($D$2:$D$8=MAX($D$2:$D$8))),ROWS(F$2:F2)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IF(F2="","",INDEX($A$2:$A$8,AGGREGATE(14,6,(ROW($A$2:$A$8)-MIN(ROW($A$2:$A$8))+1)/(($B$2:$B$8=F2)*($C$2:$C$8>50)*($D$2:$D$8=MAX($D$2:$D$8))),COUNTIFS(F$2:F2,F2))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Hopefully one last wrinkle. It gave the top 10 which is awesome. However, not sure how it is determined for a tie. So Dental, Podiatry, and Urology are at 8.90%. There aren't any other numbers after the 0. So is there a way to determine which one has the highest number of visits and give that percentage vs. the lower one.
[TABLE="width: 453"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]180 - (180) DENTAL[/TD]
[TD="align: right"]8.90%[/TD]
[TD="align: right"]107[/TD]
[/TR]
[TR]
[TD]411 - (411) PODIATRY[/TD]
[TD="align: right"]8.90%[/TD]
[TD="align: right"]79[/TD]
[/TR]
[TR]
[TD]414 - (414) UROLOGY[/TD]
[TD="align: right"]8.90%[/TD]
[TD="align: right"]49[/TD]
[/TR]
</tbody>[/TABLE]

Podiatry ended up showing up on the report, but was hoping Dental would show up.

My final table looks like this:
[TABLE="width: 331"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Stop Code[/TD]
[TD]No Show Rate (Combined)[/TD]
[TD]No Shows (Combined)[/TD]
[/TR]
[TR]
[TD](560) SUB USE DISORDER GROUP[/TD]
[TD="align: right"]26.20%[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD](513) SUB USE DISORDER INDIV[/TD]
[TD="align: right"]21.30%[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD](550) MH CLINIC GROUP[/TD]
[TD="align: right"]16.80%[/TD]
[TD="align: right"]101[/TD]
[/TR]
[TR]
[TD](502) MH CLINIC IND[/TD]
[TD="align: right"]16.50%[/TD]
[TD="align: right"]518[/TD]
[/TR]
[TR]
[TD](534) MH INTGRTD CARE IND[/TD]
[TD="align: right"]14.40%[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD](160) CLINICAL PHARMACY[/TD]
[TD="align: right"]13.90%[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD](408) OPTOMETRY[/TD]
[TD="align: right"]11.60%[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD](205) PHYSICAL THERAPY[/TD]
[TD="align: right"]11.60%[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD](323) PRIMARY CARE/MEDICINE[/TD]
[TD="align: right"]10.90%[/TD]
[TD="align: right"]659[/TD]
[/TR]
[TR]
[TD](411) PODIATRY[/TD]
[TD="align: right"]8.90%[/TD]
[TD="align: right"]79
[/TD]
[/TR]
</tbody>[/TABLE]

My codes show as such:

[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B86[/TH]
[TD]=IF(C86="","",INDEX($R$6:$R$1162,AGGREGATE(14,6,(ROW($R$6:$R$1162)-MIN(ROW($R$6:$R$1162))+1)/(($W$6:$W$1162=C86)*($X$6:$X$1162>50)*($U$6:$U$1162=MAX($U$6:$U$1162))),COUNTIFS(C$86:C86,C86))))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C86[/TH]
[TD]=IFERROR(AGGREGATE(14,6,$W$6:$W$1162/(($X$6:$X$1162>50)*$U$6:$U$1162=MAX($U$6:$U$1162)),ROWS(C$86:C86)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D86[/TH]
[TD]=IF(C86="","",INDEX($X$6:$X$1162,AGGREGATE(14,6,(ROW($X$6:$X$1162)-MIN(ROW($X$6:$X$1162))+1)/(($W$6:$W$1162=C86)*($X$6:$X$1162>50)*($U$6:$U$1162=MAX($U$6:$U$1162))),COUNTIFS(C$86:C86,C86))))[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 100%"]
<thead></thead><tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

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