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]

Hi, here is an option you can try:

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

Excel 2013/2016
	A	B	C	D	E	F
1	Service	percentage	Number of visits		%	Service
2	Cardiology	15%	65		16%	Medicine
3	Medicine	16%	75		15%	Cardiology
4	Surgery	17%	30
Sheet1

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

 

[pjandshelly]

Hi, here is an option you can try:

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

Excel 2013/2016

	A	B	C	D	E	F
1	Service	percentage	Number of visits		%	Service
2	Cardiology	15%	65		16%	Medicine
3	Medicine	16%	75		15%	Cardiology
4	Surgery	17%	30

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

Sheet1​

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

<tbody>
</tbody>

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.

 

[FormR]

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"?

 

[pjandshelly]

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.

 

[pjandshelly]

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.

 

[FormR]

It is a custom format and only pulling the month and year with Apr-19.

Hi, so are all the dates the 1st of the month or do they contain other days?

 

[pjandshelly]

Hi, so are all the dates the 1st of the month or do they contain other days?

yes,all dates are the 1st of the month.

 

[FormR]

Ok - you can give this a try..

Excel 2013/2016
	A	B	C	D	E	F	G
1	Service	percentage	Number of visits	Date		%	Service
2	Cardiology	20%	65	01-Apr-2019		25%	other
3	Medicine	25%	75	01-Apr-2019		16%	Medicine
4	Surgery	60%	30	01-Apr-2019		15%	Cardiology
5	Cardiology	15%	99	01-May-2019
6	Medicine	16%	75	01-May-2019
7	Surgery	17%	30	01-May-2019
8	other	25%	99	01-May-2019
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="","",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))))

 

[pjandshelly]

Ok - you can give this a try..

Excel 2013/2016

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

<colgroup><col style="width: 25pxpx"><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="","",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)))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.

180 - (180) DENTAL	8.90%	107
411 - (411) PODIATRY	8.90%	79
414 - (414) UROLOGY	8.90%	49

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

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

My final table looks like this:

Stop Code	No Show Rate (Combined)	No Shows (Combined)
(560) SUB USE DISORDER GROUP	26.20%	68
(513) SUB USE DISORDER INDIV	21.30%	65
(550) MH CLINIC GROUP	16.80%	101
(502) MH CLINIC IND	16.50%	518
(534) MH INTGRTD CARE IND	14.40%	52
(160) CLINICAL PHARMACY	13.90%	61
(408) OPTOMETRY	11.60%	156
(205) PHYSICAL THERAPY	11.60%	121
(323) PRIMARY CARE/MEDICINE	10.90%	659
(411) PODIATRY	8.90%	79

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

My codes show as such:

Cell	Formula
B86	=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))))
C86	=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)),"")
D86	=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))))

<thead>
</thead><tbody>
</tbody>

<thead></thead><tbody>
</tbody>