Nested IF CONTAINS Statement

CFranse · 01/19/22

I am having trouble writing a formula that I believe has to be a nested statement, but I can't figure out how to get it right. If column A contains "In Process" then column B should read 20%. If column A contains "Prepped" then column B should read 50%. And, if column A contains "Complete" then column B should read 100%. My attempt to draft the formula does yield a %, however, it is concatenating the information, so that if column A reads Prepped, column B reads: 050%0. How should I change the formula below to yield only the % that reflects the current status?

Column A (Status) Column B (% Complete)

In Process 20%

Prepped 50%

Complete 100%

Current formula draft reads =IF(CONTAINS("In Process", [Account Status]:[Account Status]), "20%", "0") + (IF(CONTAINS("Prepped", [Account Status]:[Account Status]), "50%", "0") + (IF(CONTAINS("Complete", [Account Status]:[Account Status]), "100%", "0")))

Jeff Reisman · 01/20/22

https://community.smartsheet.com/discussion/comment/316727#Comment_316727

Ah, I missed the + signs in there. Plus signs will always concatenate text and add up numbers.

What you want is an IF statement containing two truly nested IF statements.

=IF(CONTAINS("In Process", [Account Status]:[Account Status]), "20%", IF(CONTAINS("Prepped", [Account Status]:[Account Status]), "50%", IF(CONTAINS("Complete", [Account Status]:[Account Status]), "100%", "")))

HOWEVER, if your Account Status column values come from a single-select dropdown, or will otherwise be an exact match to one of the three values (or blank,) then you don't even need to use the CONTAINS function in there! Keep it simple!

=IF([Account Status]@row = "In Process", "20%", IF([Account Status]@row = "Prepped", "50%", IF([Account Status]@row = "Complete", "100%", "")))

In English: If Account Status = In Process, set column B to 20%; otherwise, consider if Account Status = Prepped, and if it does, set column B to 50%; otherwise, consider if Account Status = Complete, and if it does, set column B to 100%; otherwise, leave column B blank. (Each nested IF is the negative condition for the IF statement before it!)

(Double-check your parentheses when trying these out, make sure the color-coding matches up; I could easily have miscounted!)

Jeff Reisman · 01/19/22

Change the values you are setting to parts of 1. So 20% would be .2, 50% would be .5, etc.

Then select the column your formula is in and click the % button on the tool bar, and set the number of decimal places you want.

CFranse · 01/19/22

Thank you for taking the time to try and help me, Jeff. I don't think this will fix my issue. I am not having trouble with the % reflecting, but because my formula doesn't have an "OR" components, it's concatenating the options. It might help if I switched the value if false from "0" to "A", "B", & "C" respectively, my result would read A50%C (pulling in the false value from (1), pulling the 50% from (2) and pulling the false value for (3). I only want the formula to yield the single, non-false answer.

Ex. Current formula draft reads (please note the (1), (2) and (3) are not part of the formula, but provided as reference points.

(1) =IF(CONTAINS("In Process", [Account Status]:[Account Status]), "20%", "A") +

(2) (IF(CONTAINS("Prepped", [Account Status]:[Account Status]), "50%", "B") +

(3) (IF(CONTAINS("Complete", [Account Status]:[Account Status]), "100%", "C")))

I am having trouble writing a formula that I believe has to be a nested statement, but I can't figure out how to get it right. If column A contains "In Process" then column B should read 20%. If column A contains "Prepped" then column B should read 50%. And, if column A contains "Complete" then column B should read 100%. My attempt to draft the formula does yield a %, however, it is concatenating the information, so that if column A reads Prepped, column B reads: A50%C. How should I change the formula below to yield only the % that reflects the current status?

Column A (Status) Column B (% Complete)

In Process 20%

Prepped 50%

Complete 100%

Current formula draft reads =IF(CONTAINS("In Process", [Account Status]:[Account Status]), "20%", "A") + (IF(CONTAINS("Prepped", [Account Status]:[Account Status]), "50%", "B") + (IF(CONTAINS("Complete", [Account Status]:[Account Status]), "100%", "C")))

CFranse · 01/19/22

I FIGURED IT OUT!!!! THANK YOU!

CFranse · 01/19/22

No, i didn't.

Jeff Reisman · 01/20/22

https://community.smartsheet.com/discussion/comment/316727#Comment_316727

Ah, I missed the + signs in there. Plus signs will always concatenate text and add up numbers.

What you want is an IF statement containing two truly nested IF statements.

=IF(CONTAINS("In Process", [Account Status]:[Account Status]), "20%", IF(CONTAINS("Prepped", [Account Status]:[Account Status]), "50%", IF(CONTAINS("Complete", [Account Status]:[Account Status]), "100%", "")))

HOWEVER, if your Account Status column values come from a single-select dropdown, or will otherwise be an exact match to one of the three values (or blank,) then you don't even need to use the CONTAINS function in there! Keep it simple!

=IF([Account Status]@row = "In Process", "20%", IF([Account Status]@row = "Prepped", "50%", IF([Account Status]@row = "Complete", "100%", "")))

In English: If Account Status = In Process, set column B to 20%; otherwise, consider if Account Status = Prepped, and if it does, set column B to 50%; otherwise, consider if Account Status = Complete, and if it does, set column B to 100%; otherwise, leave column B blank. (Each nested IF is the negative condition for the IF statement before it!)

(Double-check your parentheses when trying these out, make sure the color-coding matches up; I could easily have miscounted!)

Nested IF CONTAINS Statement

Best Answer

Answers

Help Article Resources

Categories