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
David BigelowStaff Operations SpecialistAsked:
Who is Participating?
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.

ste5anSenior 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';

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Math / Science

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.