If Formula Help

Options

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

Best Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Joshua Moore
    Joshua Moore ✭✭
    Answer ✓
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    @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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Joshua Moore
    Joshua Moore ✭✭
    Answer ✓
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓
    Options

    @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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!