formula for the %completion in parent

Ray Smart
Ray Smart
edited 12/09/19 inFormulas and Functions

Hi All,

I am trying to replicate the %completion formula in googlesheets in order to understand the value of a project based completion of the various subtasks.

Does smartsheets have this ability inherently?

The project guys always think they are further along on the project than they actually are and this is artificially inflating revenue and dragging out the project timelines.

I want to be able to go through each subtask individually and challenge them with the time left to complete as a cost versus the revenue that is available to be recognised on the project.

A formula to replicate the %completion of the various parents (outside of editing the actual smartsheet) would be super helpful.

Any thoughts?

Regards,

Raymond

Comments

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
@Jmirata<\/a> <\/p>

I believe this requires you to change the column type to a contact-like one. <\/p>

Then you should be able to select it in this dropdown. <\/p>

Ryan<\/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":110640,"type":"question","name":"Sum Formula containing keywords?","excerpt":"I'm trying to keep a running sum of the amount of liquid we are holding for our business's transfer log and I am not even sure where to begin! We have to log transfers (IN) and (OUT) and I would like to write a formula to keep up with this if possible. (Adding the amount in rows with IN and subtracting the amount in rows…","snippet":"I'm trying to keep a running sum of the amount of liquid we are holding for our business's transfer log and I am not even sure where to begin! We have to log transfers (IN) and…","categoryID":322,"dateInserted":"2023-09-22T01:44:27+00:00","dateUpdated":null,"dateLastComment":"2023-09-22T12:05:15+00:00","insertUserID":151299,"insertUser":{"userID":151299,"name":"lcain","url":"https:\/\/community.smartsheet.com\/profile\/lcain","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-22T12:38:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":15741,"lastUser":{"userID":15741,"name":"ker9","url":"https:\/\/community.smartsheet.com\/profile\/ker9","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-22T22:03:40+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":35,"score":null,"hot":3390731982,"url":"https:\/\/community.smartsheet.com\/discussion\/110640\/sum-formula-containing-keywords","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110640\/sum-formula-containing-keywords","format":"Rich","lastPost":{"discussionID":110640,"commentID":396586,"name":"Re: Sum Formula containing keywords?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/396586#Comment_396586","dateInserted":"2023-09-22T12:05:15+00:00","insertUserID":15741,"insertUser":{"userID":15741,"name":"ker9","url":"https:\/\/community.smartsheet.com\/profile\/ker9","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-09-22T22:03:40+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/I6ZQZFEI6SV8\/screenshot.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-09-22T12:09:07+00:00","dateAnswered":"2023-09-22T12:05:15+00:00","acceptedAnswers":[{"commentID":396586,"body":"

Hi @lcain<\/a> <\/p>

See if this formula will work for you - in a separate column\/cell. <\/p>

It should give you the balance remaining of IN - OUT<\/p>

=SUMIFS([Quantity (gal)]:[Quantity (gal)], [Reason for Opening Container]:[Reason for Opening Container], CONTAINS(\"IN\", @cell)) - SUMIFS([Quantity (gal)]:[Quantity (gal)], [Reason for Opening Container]:[Reason for Opening Container], CONTAINS(\"OUT\", @cell))<\/p>

Hope this helps! (It is not a running total but a grand total)<\/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":110624,"type":"question","name":"Could someone help me with this formula?","excerpt":"We are asking our managers to use SmartSheet to enter information about their employees (via the add form). Be are requiring them to enter the employee ID which has to be 9 digits, but many times they don't enter all the numbers. I read about helper columns to check whether or not the format is accurate and then send an…","snippet":"We are asking our managers to use SmartSheet to enter information about their employees (via the add form). Be are requiring them to enter the employee ID which has to be 9…","categoryID":322,"dateInserted":"2023-09-21T21:53:10+00:00","dateUpdated":"2023-09-22T23:36:16+00:00","dateLastComment":"2023-09-22T13:35:05+00:00","insertUserID":167225,"insertUser":{"userID":167225,"name":"Estephania","title":"People Business Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Estephania","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Z-jR2YjcHjQ!nZFQX7DhE3w!USXSZFSV9Ma","dateLastActive":"2023-09-22T19:29:53+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":166028,"lastUserID":167225,"lastUser":{"userID":167225,"name":"Estephania","title":"People Business Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Estephania","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Z-jR2YjcHjQ!nZFQX7DhE3w!USXSZFSV9Ma","dateLastActive":"2023-09-22T19:29:53+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":39,"score":null,"hot":3390724095,"url":"https:\/\/community.smartsheet.com\/discussion\/110624\/could-someone-help-me-with-this-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/110624\/could-someone-help-me-with-this-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":110624,"commentID":396597,"name":"Re: Could someone help me with this formula?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/396597#Comment_396597","dateInserted":"2023-09-22T13:35:05+00:00","insertUserID":167225,"insertUser":{"userID":167225,"name":"Estephania","title":"People Business Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Estephania","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Z-jR2YjcHjQ!nZFQX7DhE3w!USXSZFSV9Ma","dateLastActive":"2023-09-22T19:29:53+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-09-22T13:33:27+00:00","dateAnswered":"2023-09-22T01:23:13+00:00","acceptedAnswers":[{"commentID":396566,"body":"

Hello @Estephania<\/a> <\/p>

You can use a checkbox as your helper column and trigger.<\/p>

The function will be verifying the length of the value and the box will be checked if it is too short and too long, or unchecked if it is correct.<\/p>

We will use the functions LEN and IF.<\/p>

=IF(LEN([EmployeeID]@row)=9,0,1)<\/p>

The checkbox is a variable that only recognizes true or false results. 0 means False (the ID is the correct length) and 1 means True (the ID is incorrect).<\/p>

You can also use =IF(LEN(EmployeeID]@row<>9,1,0) if it's easier for you to recognize. The results will be exactly the same.<\/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":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions