Need help on Summing Values based on drop down menu that contains text options

Monikka Mann

Hi, I have a question regarding using formulas and drop down menus.

 

Similar to an FMEA, I want to have dropdown menus (High, Med, Low) for the severity, occurrance and detectibility. Then I want to have a formula that produeces a RPN (Risk Priority Number) by assigning the value 10 to High, 7 to Med and 3 to Low and finally multiplying the numbers together. 

 

Does anyone have any ideas how I could do this?

 

Thanks!

 

Monikka

Jamison

I am assuming your columns are severity, occurence, detectibility

 

Are you OK with putting the value in the selecction?

 

10 - High

7 - Med

3 - Low

 

If so, you could put in your "total" column this: 

 

=VALUE(LEFT(severity3, 1)) * VALUE(LEFT(occurrence3, 1)) * VALUE(LEFT(detectibility3, 1))

 

If you are not OK with that you could set up three columns and assign them values based on a nested IF statement - e.g., IF(severity3="high", 10, IF(severity3="med", 7, 3)). Then you would need to multiple the value of those columns.

 

 

 

 

J. Craig Williams

If your three columns are consecutive, this will also work (without adding the value to the drop down)

 

=10 ^ COUNTIF(severity21:detectibility21, "High") * 7 ^ COUNTIF(severity21:detectibility21, "Med") * 3 ^ COUNTIF(severity21:detectibility21, "Low")

 

High-High-High = 1000

Low-Low-Low = 27

Low-Med-High (in any order)  = 210

 

I hope this helps.

Craig

 

J. Craig Williams

Also, Jamison's formula has an error for High -- which has two digits.

Craig

Monikka Mann

Thanks guys! I was playing around with the LEFT formula since yes, we will have the numbers as part of the selection. I'll give it a go and let you know how it worked!

 

Monikka

J. Craig Williams

If you get stuck with the 2 digit 10 vs 1 digit 3 and 7, check out either the FIND() function (find the space) or pad the text in the drop down (03 instead of 3).

 

Craig

Monikka Mann

Thanks everyone for your great suggestions. I was in fact able to implement the formula I was looking for:

 

Project Score=VALUE([Synthesis Score]1) * (VALUE([Success Score]1) + VALUE([Alignment Score]1) + VALUE([Manufacturing Score]1) + VALUE([Resource Score]1))

 

and to find the values from each of my drop down lists I used:

 

Alignment Score=LEFT([Alignment]1, FIND(" ", [Alignment]1))

 

Monikka