If Formula Help

Joshua Moore

Hi All,

I'm new at using formulas and I'm trying to figure out how to use my status options to trigger a predefined % Status so I can use the % Status in a pie chart.

Would love some help here as I learn more about formulas.

Here's the formula I was attempting to make work.

=IF([Status]@row = "Approved", "100%"), IF([Status]@row = "More Info Required", "25%"), IF([Status]@row = "Not Approved", "0%"), IF([Status]@row = "Requested", "10%"), IF([Status]@row = "Requested", "75%")))))

I want to make this a column formula once I get this working.

Thanks,

Joshua

Andrée Starå

Hi @Joshua Moore

I hope you're well and safe!

Try something like this.

You have two conflicting parts, "Requested".

=
IF([Status]@row = "Approved", 1, 
IF([Status]@row = "More Info Required", 0.25, 
IF([Status]@row = "Not Approved", 0, 
IF([Status]@row = "Requested", 0.1, 
IF([Status]@row = "Requested", 0.75)

Smartsheet looks at the numbers in a column formatted for percentage as values between 0 and 1. You'd need to use decimal values instead for it to work 

25% = 0,25 (0.25)

50% = 0,5 (0.5)

100% = 1

Depending on your country/region, you'll need to exchange the comma for a period.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Joshua Moore

Hi Andree,

Thanks for your reply. That worked!

Here's the adjusted formula.

=IF(Status@row = "Approved", 1, IF(Status@row = "More Info Required", 0.25, IF(Status@row = "Not Approved", 0, IF(Status@row = "Requested", 0.1, IF(Status@row = "Under Review", 0.75)))))

Thanks again for helping me out. Looking forward to learning more about formulas.

Best,

Joshua

Andrée Starå

@Joshua Moore

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå

Hi @Joshua Moore

I hope you're well and safe!

Try something like this.

You have two conflicting parts, "Requested".

=
IF([Status]@row = "Approved", 1, 
IF([Status]@row = "More Info Required", 0.25, 
IF([Status]@row = "Not Approved", 0, 
IF([Status]@row = "Requested", 0.1, 
IF([Status]@row = "Requested", 0.75)

Smartsheet looks at the numbers in a column formatted for percentage as values between 0 and 1. You'd need to use decimal values instead for it to work 

25% = 0,25 (0.25)

50% = 0,5 (0.5)

100% = 1

Depending on your country/region, you'll need to exchange the comma for a period.

Did that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Joshua Moore

Hi Andree,

Thanks for your reply. That worked!

Here's the adjusted formula.

=IF(Status@row = "Approved", 1, IF(Status@row = "More Info Required", 0.25, IF(Status@row = "Not Approved", 0, IF(Status@row = "Requested", 0.1, IF(Status@row = "Under Review", 0.75)))))

Thanks again for helping me out. Looking forward to learning more about formulas.

Best,

Joshua

Andrée Starå

@Joshua Moore

Excellent!

Happy to help!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!