do a calculation based on drop dowm list

Options

Hi,

I have same data comming from differents drop down lists and I am trying to do some calulation based on them, each list represent a certain % in my calulation. I have hard time to do any type of caluclation with them, please help if you have an idea.

Answers

  • Genevieve P.
    Options

    Hi @Otman

    Can you post a screen capture of all the values in your dropdown list, and let us know what you want each selection to represent?

    ex:

    Dropdown option: "Value 1" = 20%

    Dropdown option: "Value 2" = 40%


    Then we can write a Nested IF statement that says, if the column is "Value 1", return 0.2 (or 20%).The formula would looks something like this:

    =IF([Dropdown Column]@row = "Value 1", 0.2, IF([Dropdown Column]@row = "Value 2", 0.4, IF([Dropdown Column]@row = "Value 3", 0.6, IF([Dropdown Column]@row = "Value 4", 0.8, IF([Dropdown Column]@row = "Value 5", 1)))))

    Does that make sense?

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Otman
    Otman ✭✭
    Options

    Hi Genevieve,


    Thank you for your answer, I would like to do a further caluculation based on va;lues obtained by using a formula in Smart sheet such as

    =IF(Impact@row = "Negligible"; "1"; IF(Impact@row = "Minor"; "2"; IF(Impact@row = "Moderate"; "3"; IF(Impact@row = "Significant"; "4"; IF(Impact@row = "Severe"; "5")))))


    Thank your for your help, I highly appreciated

  • Genevieve P.
    Options

    Hi @Otman

    I see you have quotes around your numbers in the formula. This means they're being returned as text, not as numbers, which is why your other calculation won't work.

    Instead of:

    =IF(Impact@row = "Negligible"; "1"

    use:

    =IF(Impact@row = "Negligible"; 1 < No Quotes


    Ex:

    =IF(Impact@row = "Negligible"; 1; IF(Impact@row = "Minor"; 2; IF(Impact@row = "Moderate"; 3; IF(Impact@row = "Significant"; 4; IF(Impact@row = "Severe"; 5)))))


    You will need to do the same for the formula in the Risk Values column. Does that make sense?

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

  • Otman
    Otman ✭✭
    Options

    Hi Genevieve,

    It works, thank you very much for your help, have a nice day


    Best regards

  • Otman
    Otman ✭✭
    Options

    Hi Genevieve,


    I have another questions, and I would like you to help me on it (Calculation from Smartsheet forms via different smartsheet), thank you in advance for your help and amability.


    Best regards

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!