Multi select formula help to calculate based on selection(s)

AlmaJH

Hello,

We've just set up our Portfolio Intake sheet and are working on refining our WSJF columns. To make this easier for our stakeholders, we've been asked to really simplify this by identifying criteria for each column that the stakeholder can select. For example, for User/Business Value, we have 5 criteria listed in a multi select cell. For each criteria selected, we want to allocate a score in the cell, with 2 points for each criteria selected and up to 10 points if all 5 are selected.

What would such a formula look like??? So, if the requestor clicks two of the criteria below, how do we calculate a Business Value of 4 on a scale of 10-2. The stakeholder will be completing this information in an Intake form, and when the data populates in the sheet, we want to see a value of 4 populated. This value is used in the Cost of Delay formula which is already set up and is a simple sum of Business Value, Time Criticality and Risk/Opportunity. Each of those 3 columns has a similar multi select with 5 criteria.

Any suggestions?

JIDEATTURRA

In a separate column, have this formula:

=IF(CONTAINS("Test 1", [Business Value]@row), 2, 0) + IF(CONTAINS("Test 2", [Business Value]@row), 2, 0) + IF(CONTAINS("Test 3", [Business Value]@row), 2, 0) + IF(CONTAINS("Test 4", [Business Value]@row), 2, 0) + IF(CONTAINS("Test 5", [Business Value]@row), 2, 0)

Change the Test 1 - Test 5 to your drop down values.. and if you wish to change the weighting of each option, amend the 2 to whatever you like.

IF THIS HELPED, PLEASE UPVOTE, IT HELPS ME & OTHER USERS FIND ANSWERS.