I am trying to create a formula to calculate project ranking, but don't know where to start!

Alicia D

I am working for a formula to determine ranking based on some questions that people need to fill out in a form. Here are the questions they need to answer, as well as the options:

Financial Resources:

CapEx project or over $25k$5k - $25kLittle to no expense

Internal/ External Resources:

External resourcesMultiple resources on our teamMin resources

Business Value Added:

High value: Significantly enhances business performance or revenue.Medium value: Provides moderate improvements to business performance or revenue.Low value: Offers minimal improvements to business performance or revenue

Customer Impact:

High impact: Greatly improves customer satisfaction or experience.Medium impact: Moderately improves customer satisfaction or experience.Low impact: Slightly improves or has no effect on customer satisfaction or experience.

Business Risk:

High risk: Preventing significant financial risk or liability.Medium risk: Some potential for financial impact or liability.Low risk: Minimal to no potential for financial impact or liability.

Culture Change:

High change: Requires substantial changes to company culture or employee behavior.Medium change: Requires moderate changes to company culture or employee behavior.Low change: Requires minimal or no changes to company culture or employee behavior.

Level of Difficulty:

Low difficulty: Easy to implement with minimal challenges.Medium difficulty: Some challenges expected during implementation.High difficulty: Significant challenges expected during implementation.

I am not even sure if this is possible, but I am really hoping there is a way to use all of these to calculate a ranking for the projects. Thank you in advance!

Jgorsich

Figure out, on pen and paper or on a dummy spreadsheet, exactly how you want the ranking to work - for example, just based on Culture Change and Level of Difficulty - how to you rank something that is High Change, Low Difficulty vs something that is Low Change, High Difficulty or something that is Medium Change, Medium Difficulty? Are they just given numeric scores and added? Are the scores for Cultural Change weighted more heavily than difficulty or vise versa?

Once you know that, then it is just a matter of changing each value to a number and combining them per your newly developed scheme - for that you can use if() statements to score them, or have the form put out numeric results directly (since you are using a form, this is probably best) or any number of other methods.

As far as your question goes - yes, there are LOTS of ways to do what you want to do here, but all of them require you to develop your scoring methodology first (basically, to know how you were going to do it if you were doing it pen and paper) and possibly figure out what you want your input to look like.

If that seems overwhelming, start here - if you were going to be ranking projects on only a SINGLE criteria, which would it be and how would you rank them. Once that is done, if you were going to only pick TWO criteria, now you've got 9 possible combinations (3 choices per criteria) - how would you rank from those? From that, you can start figuring out how to represent it mathematically/logically.