# 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
LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Senior DeveloperCommented:
So, what is your concrete problem?

Just store the factors with your issues and create your matrix table.

DECLARE @Matrix TABLE ( TypeCode CHAR(2), RowNumber INT, ColumnNumber INT, ValueNumber INT );

INSERT INTO @Matrix
VALUES	( 'IE', 1, 1, 1 ) ,
( 'IE', 1, 2, 3 ) ,
( 'IE', 2, 1, 2 ) ,
( 'IE', 2, 2, 23 ) ,
( 'TM', 1, 1, 1 ) ,
( 'TM', 1, 2, 3 ) ,
( 'TM', 2, 1, 2 ) ,
( 'TM', 2, 2, 23 );

DECLARE @Issues TABLE ( IssueID INT, InternalNumber INT, ExternalNumber INT, TimeNumber INT, MissionNumber INT );

INSERT INTO @Issues
VALUES	( 1, 1, 1, 1, 1 ),
( 2, 2, 2, 2, 2 ),
( 3, 1, 2, 1, 2 ),
( 4, 2, 1, 1, 2 ),
( 5, 2, 2, 1, 1 );

SELECT	I.IssueID,
IE.ValueNumber + TM.ValueNumber AS IssueScoreNumber
FROM	@Issues I
INNER JOIN @Matrix IE ON I.InternalNumber = IE.RowNumber AND I.ExternalNumber = IE.ColumnNumber AND IE.TypeCode = 'IE'
INNER JOIN @Matrix TM ON I.TimeNumber = TM.RowNumber AND I.MissionNumber = TM.ColumnNumber AND TM.TypeCode = 'TM';

Experts Exchange Solution brought to you by