Ranking by multiple criteria

[JPEliz_Sx]

Hello. I could use some help with a small work project.

I have a set of data and need to determine the rank of each "application" (1 - 22) based on several sets of criteria. The data set is pictured below

The ranking of the applications is based on the following levels of criteria. Level 1 takes precedence over level 2 & 3 and so on. A rank of 1 would mean that application had the lowest number S HRS - R HRS that was less than or equal to 20 and if there were two applications with those numbers it would then prioritize by process priority (the higher priority receiving ranking 1).

Level 1: S HRS - R HRS <= 20 by process priority
Level 2: PH HRS <= 12 by process priority
Level 3: by process priority based on R HRS high to low

Any help would be greatly appreciated. Please feel free to ask questions if I need to clarify. Thank you.

 

[Eric W]

Welcome to the MrExcel forum!

I still have some questions, but here's a stab at it:

Book1
	A	B	C	D	E	F	G	H	I
1	Application	PH HRS	R HRS	S HRS	Process	Rank		Priority	Process
2	1	14	50	236	1011	22		1	D1001
3	2	12	104	236	1011	9		2	1133
4	3	12	4	170	937	16		3	1724
5	4	12	151	170	937	4		4	1178
6	5	12	236	236	1011	2		5	1011
7	6	12	54	236	865	17		6	1119
8	7	12	162	236	1011	9		7	1730
9	8	11	140	245	1724	7		8	937
10	9	12	118	236	1011	9		9	865
11	10	12	17	264	1178	8
12	11	13	169	250	1178	21
13	12	11	165	230	1119	14
14	13	11	54	264	1133	6
15	14	12	29	250	D1001	5
16	15	12	8	230	1119	14
17	16	12	245	245	1724	1
18	17	12	137	236	865	17
19	18	12	6	236	1011	9
20	19	12	23	236	865	17
21	20	12	236	236	1011	2
22	21	12	186	236	1011	9
23	22	12	122	236	865	17
Sheet5

Cell Formulas
Range	Formula
F2:F23	F2	=MATCH(IF(D2-C2<=20,10,IF(B2<=12,20,30+(1-C2/1000)))+MATCH(E2,$I$2:$I$10,0),SORT(IF(D$2:D$23-C$2:C$23<=20,10,IF(B$2:B$23<=12,20,30+(1-C$2:C$23/1000)))+MATCH(E$2:E$23,$I$2:$I$10,0)),0)

It would help if you could give the expected results, or explain if some of the values don't match your expectation. This formula will work in 365, but not 2019.

 

[JPEliz_Sx]

Thank you very much for your reply. After reviewing the results I have realized I made several mistakes in my request :/. Please forgive me I will repost here. If you don't mind, and have the time to update the formula it would be greatly appreciated.


Hi all! First post. New to the forum. I'm fairly new to excel, maybe 3 - 4 months of studying and creating basic dashboards, etc. After putting in several hours trying to figure this one particular task out I've decided to seek help from an expert.

I have a set of data and need to determine the rank of each "application" (1 - 22) based on several sets of criteria.

The ranking of the applications is based on the following levels of criteria.

Level 1: S HRS - R HRS less than or equal to 20 by process priority (lowest to highest priority: 9 to 1). Explanation: [S HRS ] Minus [R HRS] = X. If X is tied, tie break using Process Priority.

Level 2: R HRS less than or equal to PH HRS by process priority (lowest to highest priority: 9 to 1). Explanation: If [R HRS] is less then or equal to [PH HRS] than rank by process priority.

Level 3: Rank (Group) by process priority (lowest to highest priority: 9 to 1) then within that rank by R HRS (highest to lowest). Explanation: After level 1 & 2 the remaining rankings should be first based on process priority (all priority 9 should be placed before 8, all 8 before 7, and so forth). Within those rankings there may be several priority 9's. These should be ranked from largest R HRS to smallest before moving to priority 8 and so on.

Level 1 takes precedence over level 2 & 3. Level 2 takes precedence over level 3.

Ex. A rank of 1 would mean that application had the lowest number S HRS - R HRS that was less than or equal to 20. If there were two applications that were equal to each other it would then prioritize by process priority (the lower priority receiving ranking 1).

The formula must be able to update as all the criteria values change (application process may change, R HRS, S HRS, etc.). Also if it's not to much to ask an explanation of the formula would be extremely helpful too; I'd love to be able to learn while getting help. Thank you.

 

[Joe4]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Ranking by multiple criteria
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Eric W]

Hi JPElix_Sx,

I see you marked my answer as a solution. I haven't had a chance to answer your questions from post #3. Here's a quick explanation of how the formula works. I create a number representing the various criteria. The first digit represents your first criteria. If S HRS - R HRS < 20, I assign the first digit to be 3 (30 in the formula), if R HRS <= PH HRS, I assign the first digit to be 2, otherwise, the first digit is 1. The second digit of the number is the process priority, which is the second thing to check in all 3 criteria. Then for Criteria 3, I add a decimal of the R HRS to rank each line item within the process priority. I actually add a decimal of (1-R HRS/1000) to reverse the order. That may be wrong, you may want to remove the 1- from the formula (2 places). Also, if you have more process priorities than 9, you'll need to change the 30 to 300 and 20 to 200 and 10 to 100.

That's basically the whole trick. So the formula calculates that value for the current row, then it calculates that value for all rows, then it sorts the table of all rows, then does a MATCH of the current row within the sorted table, which gives the position (ranking).

Let me know if you have any other questions.

 

[JPEliz_Sx]

That explains it perfectly Eric W. Thank you very much. Sorry for marking it complete early; I had a message pop up telling me not to forget to mark it complete; I probably misread it.

Either way. Thank you for taking the time to explain this to me. It's greatly appreciated.