# Ratio-Based Conditional Task Health Indicator

Options
edited 06/12/23

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.

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

Come Say Hello!

«1

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

Come Say Hello!

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

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

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

Come Say Hello!

• Options

Thanks again, Ryan. Awesome.

• ✭✭✭✭✭✭
Options

You bet Dom! Happy to help 😀

Ryan Sides

Come Say Hello!

• 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

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

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

@Genevieve P. is spot on as always 😀

Thank you @Genevieve P. !

Ryan Sides

Come Say Hello!

• Employee
Options

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

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

Ryan Sides

Come Say Hello!

• Options

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

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

• ✭✭✭✭✭✭
edited 06/13/23
Options

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.

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