Need Help with Formula to Roll Up and Average Schedule Health.
Need Help with Formula to Roll Up and Average Schedule Health.
I have one that now updates the Schedule Health RYG based on the Start Date/End Date and Task Status.
Next, I am trying to roll up/average those Individual Task schedule health values to an overall Phase schedule health value and then ultimately to the Overall Project schedule health.
Current formula: Overall Project Schedule Health would never move from Green unless we were past the final End Date
Baseline model: Once you go red, you can never get back to green or even yellow
Any insight would be much appreciated.
Best Answer
-
Debbie Sawyer ✭✭✭✭✭✭You will need to write a nested IF function to deal with the levels of hierarchy in your RAG column.
You will have 1 set of IF's to deal with the Child Rows (usually based on Date and % complete etc)
Then another set of IF's to deal with Parent Rows (usually based on if there is a Red ball in the children then the parent is red, if there is a yellow one, then parent is yellow etc)
If the Project level (row1) logic needs to be different then a set of IF's for that will also be required.
For example:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow"))), IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))
The first half of this is dealing with Parent Rows:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow")))
The opening condition: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, is asking if there are any children (i.e. is this a Parent row) then this particular formula is then saying if it is a parent row then, if all the children are Blue then the Parent is also Blue, if all the children are Green then the Parent is also Green, if ANY of the children are Red then the Parent is also Red, or for anything else the Parent is Yellow.
The second half is child rows:
, IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))
If the row is NOT a parent row then if % Complete = 100% then Green, if the row is Overdue then Red, if the % Complete is still 0 then Blue otherwise it is set to Yellow.
I hope this helps...
Kind regards
Debbie
Answers
-
Itai ✭✭✭✭✭Hey@BArmendariz
Can you share some screenshots please?
Itai Perez
Project Manager | Transformation Department
Gong cha
-
Paul Newcome ✭✭✭✭✭✭Screenshots and your existing formula(s) would be very helpful.
-
Debbie Sawyer ✭✭✭✭✭✭
You will need to write a nested IF function to deal with the levels of hierarchy in your RAG column.
You will have 1 set of IF's to deal with the Child Rows (usually based on Date and % complete etc)
Then another set of IF's to deal with Parent Rows (usually based on if there is a Red ball in the children then the parent is red, if there is a yellow one, then parent is yellow etc)
If the Project level (row1) logic needs to be different then a set of IF's for that will also be required.
For example:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow"))), IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))
The first half of this is dealing with Parent Rows:
=IF(COUNT(CHILDREN([Task Name]@row)) > 0, IF(COUNTIF(CHILDREN(), "Blue") = COUNT(CHILDREN()), "Blue", IF(COUNTIF(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green", IF(COUNTIF(CHILDREN(), "Red") > 0, "Red", "Yellow")))
The opening condition: =IF(COUNT(CHILDREN([Task Name]@row)) > 0, is asking if there are any children (i.e. is this a Parent row) then this particular formula is then saying if it is a parent row then, if all the children are Blue then the Parent is also Blue, if all the children are Green then the Parent is also Green, if ANY of the children are Red then the Parent is also Red, or for anything else the Parent is Yellow.
The second half is child rows:
, IF([% Complete]@row = 1, "Green", IF([End Date]@row < TODAY(), "Red", IF([% Complete]@row = 0, "Blue", "Yellow"))))
If the row is NOT a parent row then if % Complete = 100% then Green, if the row is Overdue then Red, if the % Complete is still 0 then Blue otherwise it is set to Yellow.
I hope this helps...
Kind regards
Debbie
-
Here is my original formula. This one works as expected, just looking for a rollup at the phase level.
=IF(AND([Start Date]@row > TODAY(), Status@row = "Not Started"), "Gray", IF(OR(AND([% Complete]@row <= 0.25, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), AND([% Complete]@row <> 1, [End Date]@row < TODAY())), "Red", IF(AND([% Complete]@row <= 0.5, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), "Yellow", IF(AND([% Complete]@row >= 0.75, [End Date]@row >= TODAY(), [End Date]@row <= TODAY(7)), "Green", "Green"))))
-
Thank you, trying now. I had tried a few things, but hadn't considered the word "children." I'll come back and let everyone know how it works out.
-
Debbie Sawyer ✭✭✭✭✭✭
To be honest, I pop in a helper column called Parent which is a checkbox, then I simplify the formula to say:
=IF(Parent@row =1, insert nested IF's here for Parent logic, insert nested IF's here for Child logic))
Good luck!
Kind regards
-
This is what we ended up with, and it worked beautifully.
=如果(计数(儿童())> 0,如果条件统计(儿童(),"Gray") = COUNT(CHILDREN()), "Gray", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 0.5, "Red", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 1.5, "Yellow", IF((COUNTIF(CHILDREN(), "Yellow") + (COUNTIF(CHILDREN(), "Green") * 2)) / COUNTIF(CHILDREN(), <>"Gray") <= 2.5, "Green", "Gray")))), IF([% Complete]@row >= 1, "Green", IF(OR([Start Date]@row > TODAY(), ISBLANK([End Date]@row)), "Gray", IF([End Date]@row < TODAY(), "Red", IF([End Date]@row < WORKDAY(TODAY(), 3), "Yellow", "Green")))))
Then right-click on the column and select "Convert to Column Formula"
-
Debbie Sawyer ✭✭✭✭✭✭
-
Hello
Use a weighted average approach to calculate overall project schedule health. Assign weights to tasks based on importance. Multiply task schedule health by weight for each task, then average for phases and overall project.
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//m.santa-greenland.com/community/discussion/107890/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":54,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
