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.
 
one last wrinkle.

You could try like this:


Excel 2013/2016
ABCDEFGH
1ServicepercentageNumber of visitsDate%VistsService
2Cardiology20%6501-Apr-1925%99Dental
3Medicine25%7501-Apr-1925%75Medicine
4Surgery60%3001-Apr-1915%55Cardiology
5Cardiology15%5501-May-19
6Medicine25%7501-May-19
7Surgery20%2501-May-19
8Dental25%9901-May-19
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="","",AGGREGATE(14,6,$C$2:$C$8/(($C$2:$C$8>50)*($D$2:$D$8=MAX($D$2:$D$8))*($B$2:$B$8=F2)),COUNTIFS(F$2:F2,F2)))
H2=IF(F2="","",INDEX($A$2:$A$8,AGGREGATE(15,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))*($C$2:$C$8=G2)),COUNTIFS(F$2:F2,F2,G$2:G2,G2))))
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
You could try like this:

Excel 2013/2016
ABCDEFGH
ServicepercentageNumber of visitsDate%VistsService
Cardiology
MedicineMedicine
SurgeryCardiology
Cardiology
Medicine
Surgery
Dental

<colgroup><col style="width: 25pxpx"><col><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-19[/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] , align: right"]99[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] "]Dental[/TD]

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

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

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

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

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

[TD="align: right"]15%[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]01-May-19[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]25%[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]01-May-19[/TD]
[TD="align: right"][/TD]

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

[TD="align: right"]20%[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]01-May-19[/TD]
[TD="align: right"][/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-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/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="","",AGGREGATE(14,6,$C$2:$C$8/(($C$2:$C$8>50)*($D$2:$D$8=MAX($D$2:$D$8))*($B$2:$B$8=F2)),COUNTIFS(F$2:F2,F2)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H2[/TH]
[TD="align: left"]=IF(F2="","",INDEX($A$2:$A$8,AGGREGATE(15,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))*($C$2:$C$8=G2)),COUNTIFS(F$2:F2,F2,G$2:G2,G2))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Cell G2 came back with values that didn't match the service and percentage. Not sure where the numbers are pulling from. This is my script:

=IF(C86="","",AGGREGATE(14,6,(ROW($X$6:$X$1162)/($X$6:$X$1162>50)*($U$6:$U$1162=MAX($U$6:$U$1162))*($W$6:$W$1162=C86)),COUNTIFS(C$86:C86,C86)))
the Values that came back were:

[TABLE="width: 384"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Stop Code[/TD]
[TD]No Show Rate (Combined)[/TD]
[TD]No Shows (Combined)[/TD]
[TD]# that it should have been[/TD]
[/TR]
[TR]
[TD](560) SUB USE DISORDER GROUP[/TD]
[TD="align: right"]26.20%[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]68[/TD]
[/TR]
[TR]
[TD](513) SUB USE DISORDER INDIV[/TD]
[TD="align: right"]21.30%[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD](550) MH CLINIC GROUP[/TD]
[TD="align: right"]16.80%[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]101[/TD]
[/TR]
[TR]
[TD](502) MH CLINIC IND[/TD]
[TD="align: right"]16.50%[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]518[/TD]
[/TR]
[TR]
[TD](534) MH INTGRTD CARE IND[/TD]
[TD="align: right"]14.40%[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]52[/TD]
[/TR]
[TR]
[TD](160) CLINICAL PHARMACY[/TD]
[TD="align: right"]13.90%[/TD]
[TD="align: right"]27[/TD]
[TD="align: right"]61[/TD]
[/TR]
[TR]
[TD](205) PHYSICAL THERAPY[/TD]
[TD="align: right"]11.60%[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]156[/TD]
[/TR]
[TR]
[TD](408) OPTOMETRY[/TD]
[TD="align: right"]11.60%[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD](323) PRIMARY CARE/MEDICINE[/TD]
[TD="align: right"]10.90%[/TD]
[TD="align: right"]49[/TD]
[TD="align: right"]659[/TD]
[/TR]
[TR]
[TD](180) DENTAL[/TD]
[TD="align: right"]8.90%[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]107[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
This is my script:

=IF(C86="","",AGGREGATE(14,6,(ROW($X$6:$X$1162)/($X$6:$X$1162>50)*($U$6:$U$1162=MAX($U$6:$U$1162))*($W$6:$W$1162=C86)),COUNTIFS(C$86:C86,C86)))

Hi, that doesn't look like the suggested formula in post#11
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

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