Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Need help on Summing Values based on drop down menu that contains text options
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
Comments

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.

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")
HighHighHigh = 1000
LowLowLow = 27
LowMedHigh (in any order) = 210
I hope this helps.
Craig 
Also, Jamison's formula has an error for High  which has two digits.
Craig 
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

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

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