LinkBack URL
About LinkBacks
Ranking by multiple criteria
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. Please see attached file.
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.
Register To Reply
Bookmarks