Template question

Shawn Borror

Product Prioritization Matrix Template

I have a question about this template - specifically the priority column. It doesn't seem to be formula based - but can anyone tell me how this is used and how it's used specific to the template examples?

Genevieve P.

Hi Shawn,

You are correct, the template does not have a pre-built formula, so the "PRIO" section does not automatically flag anything.

You could base the Priority symbols on the "Total Score" column by inserting a formula similar to this:

=IF([Total Score]@row >= 60, "High", IF([Total Score]@row >= 40, "Medium", "Low"))

This would return an exclamation mark if the score was equal to or above 60 for that row, a yellow line if the score was under 60 but equal to or above 40, and a blue down arrow if the score was below 40. Drag-fill the formula down the entire column so new entries will then automatically receive a priority rating based on the total score of their criteria.

You can of course adjust what total score equates a Priority by adjusting the numbers in the formula. Does that work for your purposes, or do you have a different type of priority you are looking to record?

Genevieve P.

Hi Shawn,

If you double-click on a cell under the Total Score column, you'll see that there's quite a large SUM(VLOOKUP formula present in the sheet. This formula adds together each score from that row (hence the SUM). But it also weighs this, based on what you have input in the Criteria Score Key and also the designated weight in the orange "Criteria Weight Values" row.

For example: in your screen capture, in row 27 your Customer Value is set to 5.

The Key at the top lets you know that 5 = 100%.

The Criteria Weight Value for the "Customer Value" is set to 10 (or 10/100, which is a weight of 10% of all the different criteria).

The formula in the Total Score column does the following:

5 = 100% (by looking at the Score Key with VLOOKUP)

100% x 10 = 10 (by multiplying the Score Key percent with the weight of that Criteria)

10 is the overall number to be added in to the total, with the same formula doing the same calculation to find the weight of the other criteria, and present you with a weighted total.

If you click on the comment box to the left of Row 8 you will see a comment explaining that you can change the percents in the Score Key based on how you would like to weigh your numbers, but do not move the key or it will create errors with the Total Score column.

Hope this helps!

Genevieve

Genevieve P.

Hi Shawn,

Are you using the first template found on this page: https://www.smartsheet.com/priority-matrix-templates? If so, this page actually has some good information about how to use the Priority symbol column in the rest of the article:

There isn’t a standard formula for creating a total priority score to rank your requirements. Tailor your formula and the weighting criteria and range of assessment value to your team’s focus and the purpose of the evaluation. Wieger uses the following formula: Requirement priority = value% divided by (cost% x cost weight + risk% x risk weight). 

Essentially this means that the column can be formula based, but since the way you want to prioritize will be customized to your specific goals, there's no standard formula for that column set up in the template. You would need to build out a customized IF statement to determine when you would like each symbol to appear.

If you need help creating the formula, it would be good to see a screen capture of the sheet you're working within (remove any sensitive data), along with a list of what criteria you want to be the determining factors for each symbol.

Cheers!

Genevieve

Shawn Borror

Hi yes, that is the template set I"m using --

So in the example sheet does that mean it's not automatically flagged currently?

Shawn Borror

My question then even if I use a formula I'm not sure how to use the data that is there to tell me whether or not priority has gone up or down...

Genevieve P.

Hi Shawn,

You are correct, the template does not have a pre-built formula, so the "PRIO" section does not automatically flag anything.

You could base the Priority symbols on the "Total Score" column by inserting a formula similar to this:

=IF([Total Score]@row >= 60, "High", IF([Total Score]@row >= 40, "Medium", "Low"))

This would return an exclamation mark if the score was equal to or above 60 for that row, a yellow line if the score was under 60 but equal to or above 40, and a blue down arrow if the score was below 40. Drag-fill the formula down the entire column so new entries will then automatically receive a priority rating based on the total score of their criteria.

You can of course adjust what total score equates a Priority by adjusting the numbers in the formula. Does that work for your purposes, or do you have a different type of priority you are looking to record?

Shawn Borror

Nope that works great thank you. Next question is how do the criteria score key map to the columns below - like Customer Value/Business Value ?

Genevieve P.

Hi Shawn,

If you double-click on a cell under the Total Score column, you'll see that there's quite a large SUM(VLOOKUP formula present in the sheet. This formula adds together each score from that row (hence the SUM). But it also weighs this, based on what you have input in the Criteria Score Key and also the designated weight in the orange "Criteria Weight Values" row.

For example: in your screen capture, in row 27 your Customer Value is set to 5.

The Key at the top lets you know that 5 = 100%.

The Criteria Weight Value for the "Customer Value" is set to 10 (or 10/100, which is a weight of 10% of all the different criteria).

The formula in the Total Score column does the following:

5 = 100% (by looking at the Score Key with VLOOKUP)

100% x 10 = 10 (by multiplying the Score Key percent with the weight of that Criteria)

10 is the overall number to be added in to the total, with the same formula doing the same calculation to find the weight of the other criteria, and present you with a weighted total.

If you click on the comment box to the left of Row 8 you will see a comment explaining that you can change the percents in the Score Key based on how you would like to weigh your numbers, but do not move the key or it will create errors with the Total Score column.

Hope this helps!

Genevieve