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.

• Employee
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

October 8 - 10, Seattle, WA | Register now

• ✭✭
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")))))

• Employee
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.

=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

October 8 - 10, Seattle, WA | Register now

• ✭✭
Options

Hi Genevieve,

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

Best regards

• ✭✭
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!