Ranking values in Excel

[pjandshelly]

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.

 

[FormR]

one last wrinkle.

You could try like this:

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	Service	percentage	Number of visits	Date		%	Vists	Service
2	Cardiology	20%	65	01-Apr-19		25%	99	Dental
3	Medicine	25%	75	01-Apr-19		25%	75	Medicine
4	Surgery	60%	30	01-Apr-19		15%	55	Cardiology
5	Cardiology	15%	55	01-May-19
6	Medicine	25%	75	01-May-19
7	Surgery	20%	25	01-May-19
8	Dental	25%	99	01-May-19
Sheet1

Cell Formulas
Range	Formula
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))))

 

[pjandshelly]

You could try like this:

Excel 2013/2016

	A	B	C	D	E	F	G	H
1	Service	percentage	Number of visits	Date		%	Vists	Service
2	Cardiology	20%	65	01-Apr-19		25%	99	Dental
3	Medicine	25%	75	01-Apr-19		25%	75	Medicine
4	Surgery	60%	30	01-Apr-19		15%	55	Cardiology
5	Cardiology	15%	55	01-May-19
6	Medicine	25%	75	01-May-19
7	Surgery	20%	25	01-May-19
8	Dental	25%	99	01-May-19

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula 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)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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:

Stop Code	No Show Rate (Combined)	No Shows (Combined)	# that it should have been
(560) SUB USE DISORDER GROUP	26.20%	9	68
(513) SUB USE DISORDER INDIV	21.30%	17	65
(550) MH CLINIC GROUP	16.80%	19	101
(502) MH CLINIC IND	16.50%	22	518
(534) MH INTGRTD CARE IND	14.40%	25	52
(160) CLINICAL PHARMACY	13.90%	27	61
(205) PHYSICAL THERAPY	11.60%	45	156
(408) OPTOMETRY	11.60%	44	121
(323) PRIMARY CARE/MEDICINE	10.90%	49	659
(180) DENTAL	8.90%	60	107

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[FormR]

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

 

[pjandshelly]

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

You are right. I overlooked the beginning piece after the Aggregate. I updated it and it worked. Thank you for all of your help. This was extremely helpful.