What formula would I use for a four factor prioritization method where the factors are summed and ranked.
This question may need to go through several explanations before I get it clear, but here goes.
We are developing a way to prioritize system issues. Our current ranking is 1 - 5, but that becomes rather flat when dealing with a couple hundred issues.
In our new method, we have four factors in two sets of two. External impact and Internal impact are one set. Mission and Time are another set.
Each factor has sublevels, "criteria," that we use to score the criticality of the factor. A low score is a high ranking.
External impact has 10 criteria
Internal impact has 13 criteria
Mission has 8 criteria
Time has 15 criteria
The scores from each set are compared in a grid. This is the part that is hard to explain abstractly. If I could, I probably wouldn't be asking the question, since the solution usually lies in abstraction. I'll use a concrete example, instead. Refer to the attached grid.
Consider 2 issues:
Issue 1: External impact: 2, Internal impact 3
Issue 2: Internal impact: 2, External impact 3
Issue 1: External 2, score 24
Issue 2: Internal 2, score 25.
So the issue with an External impact of 2 will be ranked higher than an issue with an Internal impact of 2.
The same is true for Mission and Time.
If Mission 2 and Time 3 (24)
Or Time 2 and Mission 3, (25)
The Mission should always score lower (rank higher).
Then, the Mission & Time score is added to the External & Internal score to determine final ranking.
Issue 1: 24+24 = 48,
Issue 2: 25+25 = 50.
Overall, if issues tie in cumulative score, then they should be ranked by Mission, External, Internal, and Time.
The current grid is just counting linearly, alternating the row/column coordinates. I suppose there's formula in there, somewhere.
If you can simplify this process for a more simple formula than what I present, that would be pleasantly acceptable.
The final solution will need to be transferable to a database where I'll input the factor criteria and receive a ranking for each issue.
Matrix.xlsx
We are developing a way to prioritize system issues. Our current ranking is 1 - 5, but that becomes rather flat when dealing with a couple hundred issues.
In our new method, we have four factors in two sets of two. External impact and Internal impact are one set. Mission and Time are another set.
Each factor has sublevels, "criteria," that we use to score the criticality of the factor. A low score is a high ranking.
External impact has 10 criteria
Internal impact has 13 criteria
Mission has 8 criteria
Time has 15 criteria
The scores from each set are compared in a grid. This is the part that is hard to explain abstractly. If I could, I probably wouldn't be asking the question, since the solution usually lies in abstraction. I'll use a concrete example, instead. Refer to the attached grid.
Consider 2 issues:
Issue 1: External impact: 2, Internal impact 3
Issue 2: Internal impact: 2, External impact 3
Issue 1: External 2, score 24
Issue 2: Internal 2, score 25.
So the issue with an External impact of 2 will be ranked higher than an issue with an Internal impact of 2.
The same is true for Mission and Time.
If Mission 2 and Time 3 (24)
Or Time 2 and Mission 3, (25)
The Mission should always score lower (rank higher).
Then, the Mission & Time score is added to the External & Internal score to determine final ranking.
Issue 1: 24+24 = 48,
Issue 2: 25+25 = 50.
Overall, if issues tie in cumulative score, then they should be ranked by Mission, External, Internal, and Time.
The current grid is just counting linearly, alternating the row/column coordinates. I suppose there's formula in there, somewhere.
If you can simplify this process for a more simple formula than what I present, that would be pleasantly acceptable.
The final solution will need to be transferable to a database where I'll input the factor criteria and receive a ranking for each issue.
Matrix.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.