Clean-up formula

Pestomania

I am sure that is a much easier way for me to have completed this task, but to explain:

I wanted to find the statement between the parentheses and replace that with a number related to the statement. By doing so, I was able to run formulas to give a numbered response. Is there a way to clean it up?

=((VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID([Likelihood of Occurence]@row, FIND("(", [Likelihood of Occurence]@row), FIND(")", [Likelihood of Occurence]@row)), "(Very Low)", 1), "(Low)", 2), "(Moderate)", 3), "(Elevated)", 4), "(High)", 5)))) * AVG(VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID([Degree of Impact - Financial]@row, FIND("(", [Degree of Impact - Financial]@row), FIND(")", [Degree of Impact - Financial]@row)), "(Very Low)", 1), "(Low)", 2), "(Moderate)", 3), "(Elevated)", 4), "(High)", 5)), (VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID([Degree of Impact - Operational]@row, FIND("(", [Degree of Impact - Operational]@row), FIND(")", [Degree of Impact - Operational]@row)), "(Very Low)", 1), "(Low)", 2), "(Moderate)", 3), "(Elevated)", 4), "(High)", 5))), (VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID([Degree of Impact - Reputational]@row, FIND("(", [Degree of Impact - Reputational]@row), FIND(")", [Degree of Impact - Reputational]@row)), "(Very Low)", 1), "(Low)", 2), "(Moderate)", 3), "(Elevated)", 4), "(High)", 5)))) ^ 0.5

Kerry St. Thomas

Just looking at the formula, I'd suggest breaking things into the component parts:

—Create a sheet with lookup values - using two columns. Measure (Very Low / Low / etc.) and Value (1 / 2 / etc.). —Create helper columns to INDEX/MATCH against that sheet to return the value data for each measure's individual numerical value.
—Replace the formula above with math referencing the helper columns to derive the final numerical value.

This way, your tool is going to be a bit more scalable - in the event you add an additional item in your measure scale, or add additional dimensions, it'll be MUCH more straightforward to maintain.