Formula returning a blank cell
I have been trying to figure this out for hours with no luck. We've created a formula to automatically update our status column in our project plan sheets. We have specific parent rows that are designated as milestones. The milestone row status cells should update based on the status of the child rows. A milestone row is indicated in the Milestone? row by clicking the symbol (star) to fill it in. I've got the formula below to guide automated status updates. In our milestone rows, the formula is returning a blank cell, and I cannot for the life of me figure out why. I've searched around in the Community and haven't seen a similar question/response.
=IF([Milestone?]@row= 0, IF([email protected]= 1, "Blocked", IF(ISBLANK([Actual Start Date]@row), "Not Started", IF([Actual Completion Date]@row<> "", "Completed", IF([Projected End Date]@row< TODAY(), "Late", IF(NETWORKDAYS(TODAY(), [Projected End Date]@row)<= 2, "Deadline Approaching", IF([Actual Start Date]@row< TODAY(), "In Progress", "")))))) + IF([Milestone?]@row= 1, IF(COUNTIF(CHILDREN(), "Blocked") >= 1, "Blocked", IF(COUNTIF(CHILDREN(), "Late") > 1, "High Risk", IF(COUNTIF(CHILDREN(), "Deadline Approaching") >= COUNT(CHILDREN()) / 2, "Low Risk", IF(COUNTIF(CHILDREN(), "In Progress") > 0, "In Progress", IF(COUNTIF(CHILDREN(), "Complete") = COUNT(CHILDREN()), "Complete", "Not Started")))))))
Comments
-
Mike L. ✭✭One of your IF statements returns a blank cell if false. What if you change the statement from:
IF([Actual Start Date]@row< TODAY(), "In Progress", ""
to
IF([Actual Start Date]@row< TODAY(), "In Progress", "you are here"
If that doesn't produce the new message in the cell I would start over by testing each condition to see if you get the intended result. Probably the conditional statements before and after the "+" symbol, each time testing one component then rebuild the formula.
-
Paul Newcome ✭✭✭✭✭✭Are you able to provide a screenshot that covers all of the column referenced in your formula for the row in question? That may help us narrow down where an issue could be hiding.
10xViz
Help Article Resources
Categories
=IF(COUNT(CHILDREN()) = 0, IF(OR(Done@row = 1, [N\/A]@row = 1), 1, 0), AVG(CHILDREN()))<\/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":108195,"type":"question","name":"COUNTIF Function Not Working Correctly","excerpt":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this one? =COUNTIF([Account Dashboard]:[Account Dashboard], \" \")","snippet":"Hello, I have a COUNTIF function I am using to count all cells that are 'Blank' in a column. We should have one result, but the formula is showing as zero. Any thoughts on this…","categoryID":322,"dateInserted":"2023-07-27T16:41:32+00:00","dateUpdated":null,"dateLastComment":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164192,"lastUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":17,"score":null,"hot":3380962360,"url":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108195\/countif-function-not-working-correctly","format":"Rich","lastPost":{"discussionID":108195,"commentID":387638,"name":"Re: COUNTIF Function Not Working Correctly","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387638#Comment_387638","dateInserted":"2023-07-27T19:11:08+00:00","insertUserID":164192,"insertUser":{"userID":164192,"name":"ewitt19205","url":"https:\/\/community.smartsheet.com\/profile\/ewitt19205","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!mo_vH-5dWnI!5OcveUI6Zjo!UtWovaSRry_","dateLastActive":"2023-07-27T19:07:23+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-07-27T19:10:56+00:00","dateAnswered":"2023-07-27T16:47:29+00:00","acceptedAnswers":[{"commentID":387588,"body":"
