Ratio-Based Conditional Task Health Indicator

Options
Dom Orsler
Dom Orsler ✭✭
edited 06/12/23 in Formulas and Functions

Hi

I'm a bit embarrassed to ask this, as it seems so simple, yet I've been searching high and low for an answer so am resorting to posting a question. I'm relatively new to SS and find the conditional logic interface a bit clunky and hard to use. In summary, I've created a R/Y/G indicator column and am trying to work out how to apply the following logic;

If % complete is greater than or equal to 75% of the percentage of the duration elapsed, green.

If % complete is between 25% and 75% of the percentage of the duration elapsed, yellow.

If % complete is less than or equal to 25% of the percentage of the duration elapsed, yellow.

I'm trying to compare % complete to what proportion of the duration has elapsed and flag accordingly.

Everything I've managed to find so far bases indicators off of today's date in conjunction with due date and % complete, which is not what I need. I'm trying to base indicators off of ratios rather than absolutes.

Thanks.

Best Answer

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Dom, how are you looking to calculate the duration elapsed? For instance:

    Start Date = 01/01/23

    Finish Date = 06/01/23

    Today's Date = 05/29/23

    Days between Start and Finish = 151

    Days between today and start = 148

    Which is 97% (148/151) of the total available days.

    Correct?


    If so, then you can use this formula:

    =IF([% Complete]@row >= (0.75 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Green", IF([% Complete]@row > (0.24 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Yellow", "Red"))


    Is that what you were looking for?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

«1

Answers

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi Dom, how are you looking to calculate the duration elapsed? For instance:

    Start Date = 01/01/23

    Finish Date = 06/01/23

    Today's Date = 05/29/23

    Days between Start and Finish = 151

    Days between today and start = 148

    Which is 97% (148/151) of the total available days.

    Correct?


    If so, then you can use this formula:

    =IF([% Complete]@row >= (0.75 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Green", IF([% Complete]@row > (0.24 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Yellow", "Red"))


    Is that what you were looking for?

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Dom Orsler
    Options

    Thanks! I think that's getting there. I should have mentioned, obviously, these conditions will be secondary to the first condition, which would make it red if due date has passed and % complete isn't 100, as incomplete tasks past due should always be red.

    How and where do you plug this 'code' in? All I know to access is the conditional formatting button, which opens a bit of a clunky rules dialogue that's really constrained by drop downs. Is there another GUI that gives me access to background code?

  • Dom Orsler
    Options

    Told you I was a noob! ;-) Ignore my question about 'where do I type the code'. I appreciate how basic that is and have worked it out. I reckon what you've provided is enough to get me there. Thanks again!

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    edited 05/30/23
    Options

    Here you go. I added the if it hasn't finished yet piece.

    =if(and(Finish@row <today(), [% Complete]@row <1), "Red", IF([% Complete]@row >= (0.75 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Green", IF([% Complete]@row > (0.24 * (TODAY() - Start@row) / (Finish@row - Start@row)), "Yellow", "Red")))

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Dom Orsler
    Options

    Thanks again, Ryan. Awesome.

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    You bet Dom! Happy to help 😀

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Dom Orsler
    Options

    Hey Ryan


    Asking these questions is a little embarrassing, as I'm sure they're extremely basic. As a final touch to this logic, I'd now like to also turn the indicator red if the end date for the task has passed. Foolishly, I thought this would be a simple nested IF, but I can't get the syntax to parse;

    =IF(TODAY() > End@row, “Red”, (IF(Status@row <> "Done", (IF(Duration@row > 0, (IF([% Done]@row >= (0.75 * (TODAY() - Start@row) / (End@row - Start@row)), "Green", IF([% Done]@row > (0.24 * (TODAY() - Start@row) / (End@row - Start@row)), "Yellow", "Red"))))))))

    I can't work out why.

    Thanks again,

    Dom

  • Genevieve P.
    Options

    Hey @Dom Orsler

    No worries at all - that's what the Community is here for! 🙂

    I noticed in your copy/pasted formula that your quotes are a mixture of curved:

    “Red”

    and straight:

    "Done"


    Smartsheet needs quotes to be the straight ones in order to parse properly. The best way to get this is to type your formula directly into the sheet (versus copy/pasting from another platform or from notes).

    Try your same formula again, but with the proper quotes around "Red":

    =IF(TODAY() > End@row, "Red", IF(Status@row <> "Done", IF(Duration@row > 0, IF([% Done]@row >= (0.75 * (TODAY() - Start@row) / (End@row - Start@row)), "Green", IF([% Done]@row > (0.24 * (TODAY() - Start@row) / (End@row - Start@row)), "Yellow", "Red")))))

    Cheers,

    Genevieve

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

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @Dom Orsler

    Sorry for the delay. I’ve been heads down on a deadline.

    @Genevieve P. is spot on as always 😀

    Thank you @Genevieve P. !

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Genevieve P.
    Options

    Any time! Hope you made your deadline 🙂

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

  • Ryan Sides
    Ryan Sides ✭✭✭✭✭✭
    Options

    @Genevieve P. I did! Was due this morning. Last week was crazy busy so it required a few weekend hours.

    Ryan Sides

    Smartsheet Tips and Tricks for Beginners and Advanced on LinkedIn and YouTube

    Come Say Hello!

  • Dom Orsler
    Options

    Most awesome, guys, and many thanks, again. Who knew about the quotes! No wonder I was stuck in an eddy.

  • Dom Orsler
    Options

    I'm using a symbol type field for this that allows red, yellow or green buttons. I've now been asked to include an orange option. I've looked at the field properties and the options for symbols and can't find a preconfigured one with orange. Is there a way to create a custom symbol field that includes an orange option?

    Thanks again.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 06/13/23
    Options

    Hi @Dom Orsler

    I hope you're well and safe!

    You can use other symbols by using, for example, Emojis.

    Here's a page you can use.

    getemoji.com

    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, Awesome, 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.

  • Dom Orsler
    Options

    Sorry - no, that doesn't help. I need coloured buttons, not emoticons. And whether I used emoticons or coloured buttons, I don't know how to create a new button or how to modify the current one.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!