Does anyone have the formula syntax to control the RYG and Gray symbols on a sheet

Patrick Galvin

Does anyone have the formula syntax for this.... to control the RYG and Gray symbols on a sheet?

 

if due date < today and % complete <> 100% then red, 

if due date within 5 days of today and % complete <> 100% then yellow, 

if due date outside of 5 days from today % complete > 1 and < 100% then green, 

if % complete is 0 and due date outside of 5 days from now then gray

 

Mike Andreas

I think I have formulas very close to what you are looking for.  The following input columns are required (named exactly as I show):

Actual Percent Complete

Start Date

End Date

Date Completed

 

The following columns with the following equations are required (exactly as named here):

Days Until Start; =[Start Date]2 - TODAY()

 

Days Until Due; =[End Date]2 - TODAY()

 

Planned Percent Complete Per Timeline; =IF(TODAY() - [Start Date]2 < 0, 0, IF([Actual Percent Complete]2 = 1, 1, IF(((TODAY() - [Start Date]2) / ([End Date]2 - [Start Date]2)) >= 1, 1, ((TODAY() - [Start Date]2) / ([End Date]2 - [Start Date]2)))))

 

Detailed Status; =IF(AND([Actual Percent Complete]2 <> 1, [Date Completed]2 <> ""), "TASK NOT COMPLETED - DELETE EXISTING DATE", IF(AND([Actual Percent Complete]2 = 1, [Date Completed]2 = ""), "ENTER COMPLETION DATE", IF(OR(AND([End Date]2 - TODAY() < 0, [Actual Percent Complete]2 <> 1), ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) >= 0.25), "In Progress - Off Track", IF([Date Completed]2 - [End Date]2 > 0, "Completed - Late", IF(AND([Planned Percent Complete Per Timeline]2 = 1, [Actual Percent Complete]2 = 1), "Completed - On Time", IF(AND([Days Until Start]2 > 0, ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) <= 0.1), "Not Started", IF(AND(ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) > 0.1, ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) <= 0.25), "In Progress - At Risk", "In Progress - On Track")))))))

 

Status; =IF([Detailed Status]2 = "In Progress - At Risk", "Yellow", IF(OR([Detailed Status]2 = "In Progress - Off Track", [Detailed Status]2 = "TASK NOT COMPLETED - DELETE EXISTING DATE"), "Red", IF(OR([Detailed Status]2 = "Completed - On Time", [Detailed Status]2 = "Completed - Late"), "Blue", "Green")))

 

Note that the two "Percent Complete" columns need to be formatted as percent.  The status column needs to be formatted as the symbol, specifically the red, yellow, green, and blue traffic lights.  Note that these equations show red if the variance between planned and actual percent complete is more than 25%, yellow if between 10 and 25%, green if less than 10% or task hasn't been due to start yet, and blue if it is complete, either on time or late.  See screenshot included below as an example.

 

This system has been invaluable for me and my organization in keeping on top of tasks in very large sheets as time creeps along and tasks "silently" become due.  Let me know if the equations work for you.

 

Mike Andreas

Note that for the above equations to work, that they must be pasted into row 2.  Also, be sure not to include the semi-colons I included after the equation-driven column names - those could trip you up if not careful.

 

Also, if you want to use filters to pull for example, red and yellow rows that are tasks only (not parent rows that by themselves don't reflect real work), you will need the additional column with the additional equation, and select "0", since that identifies the row as being the "lowest level child".

 

Is Child?; =IF(COUNT(CHILDREN()) > 0, 1, 0)

 

Patrick Galvin

Great stuff - thanks for this - although I do have some questions...

 

https://app.smartsheet.com/b/publish?EQBCT=4bfbd872ce024348bd28806d41564bd4

 

Row 4 has zero percent for Actual Precent Complete and this gives an #INVALID OPERATION error

 

The Status RYGB icons dont change dynamically 

 

Can you summarise the AVG in the column to give an overall project Status?

 

Mike Andreas

First off, make sure the percent complete columns are formatted as percent - they didn't show the percent sign when I looked at your sheet.  It might be possible that the equations in row 3 are seeing the Actual Percent Complete as 9000% since you have 90 in the cell, which obviously is more than a 25% variance from 0%, the current value of Planned Percent Complete, and would be why the red status symbol is showing.

 

Second, change the start date of your examples to be different from each other instead of all being today.  This will let me know if the equations are seeing the data correctly (in the right format).

 

Third, it looks like you have the 1 in the Is Child? equation in the wrong spot.  This makes me wonder if you didn't copy the formula to your sheet correctly - if so, this may be what happened with the other more complex equations too.  Shouldn't affect the primary functionality you're asking about, but thought I'd point it out nonetheless.

Patrick Galvin

Hi 

 

I have updated as per your comments...

1. changed values to % and this corrted some of them

2. changed the start dates to varied dates

3. changed the end dates to varied dates

4. the IS CHILD value was simply me reversing the logic to show the sub taska as 1 and the parent as 0. I have reverted this logic now

 

Is appears that if the start date is in teh future then you get the #INVALID OPERATION error... any thoughts?

Mike Andreas

It looks like planned percent complete is still not in % format

Cecelia Martin

So I am trying to understand some of the logic you are using.

 

1. Planned Percent complete per timeline.  So I don't quite understand this calculation.  Looking at my picture below I have my actual percentage complete as 95%.  How are you getting the number 46%? I am still within the start and end dates.

 

2. And why is the detailed status In Progress-Off Track?  Again I am 95% done and within my end date window.  Why is it considered 'off-track'?

 

I really like this but just trying to understand your logic and calculations.

 

 

 

Cecelia Martin

Cecelia Martin

So playing around with this it seems my sums are off and backward.  when the Planned Percent Complete says 9% and my actual Percent Complete says 60% the Detailed status = In Progress-Off Track.

 

But when I change Actual Percent Complete to 8% (keeping Planned at 9%), detailed status = In Progress-On Track.

 

I also want when the Acutal Percentage Complete is= 0% and the start date has passed, that Detailed Status says Not Started-At Risk and turns Status to Red.

 

Here are my calculations.

 

Planned Percent Complete Per Timeline

=IF(TODAY() - [Start Date]2 < 0, 0, IF([Actual Percent Complete]2 = 1, 1, IF(((TODAY() - [Start Date]2) / ([End Date]2 - [Start Date]2)) >= 1, 1, ((TODAY() - [Start Date]2) / ([End Date]2 - [Start Date]2)))))

 

Detailed Status

=IF(AND([Actual Percent Complete]2 <> 1, [Date Completed]2 <> ""), "TASK NOT COMPLETED - DELETE EXISTING DATE", IF(AND([Actual Percent Complete]2 = 1, [Date Completed]2 = ""), "ENTER COMPLETION DATE", IF(OR(AND([End Date]2 - TODAY() < 0, [Actual Percent Complete]2 <> 1), ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) >= 0.25), "In Progress - Off Track", IF([Date Completed]2 - [End Date]2 > 0, "Completed - Late", IF(AND([Planned Percent Complete Per Timeline]2 = 1, [Actual Percent Complete]2 = 1), "Completed - On Time", IF(AND([Days Until Start]2 > 0, ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) <= 0.1), "Not Started", IF(AND(ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) > 0.1, ABS([Actual Percent Complete]2 - [Planned Percent Complete Per Timeline]2) <= 0.25), "In Progress - At Risk", "In Progress - On Track")))))))

 

Any help is appreciated.

Mike Andreas

I didn't look through the equations in detail - I assume they are not modified to what I shared initially.  However, it sounds as though the equations are working as expected -congrats!  Technically if the Actual is greater than the Planned by 25%, the task is off track (in the strictest project management sense - work is being done more quickly than planned).  In the rare event work on my projects is in this situation, I reduce the duration, or if the end date should remain, simply live with the red until the Planned "catches up".  One could very well change the equation to only show red if the Planned is 25% greater than Actual - I just don't have this need.  The equation is already long enough, I'm trying to keep it "simple".

 

As for your last additional requirement, I politely "challenge" you to see if you can add it yourself - you seem pretty astute at the equations.    I feel that if the start date passes and Actual stays 0, status will turn yellow and eventually red soon enough on its own to warrant avoiding the additional code.  It may be that instead of the variance waiting to get to 10% before yellow appears, that a lower percentage would better suit your needs.

 

Glad it is working for you!

Patrick Galvin

Hugely appreciated - thanks

 

Cecelia Martin

Mike thanks.  I am getting an error #INvalid Operation.  Do you know why?  I used your formulas and set up....

 

Patrick Galvin

I would suggest that you need to have a value other than zero in the Planned Percent Complete Per Timeline.

If you compare the row that has 6% in it this is not erroring. 

 

 

Mike Andreas

If you wouldn't mind sharing your sheet with me (mike.andreas@tarkett.com), I will take a look at it.  Make sure that you are dragging the equations down using the lower right corner of the cell as opposed to copying/pasting in each cell, which would result in those equations referring back to the values in row 2.  This may explain why your first row is working and the rest of them aren't.

Mike Andreas

I got it working - you didn't have "Date Completed" column formatted as a date - it was Text.  Voila!