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

06/30/16 Edited 12/09/19

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

  • JamisonJamison ✭✭✭✭✭

    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 WilliamsJ. Craig Williams Top Contributor

    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 WilliamsJ. Craig Williams Top Contributor
    edited 07/07/16

    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

  • J. Craig WilliamsJ. Craig Williams Top Contributor

    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

  • edited 02/17/17

    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

This discussion has been closed.