Array formulas for project completion in Smartsheet

Ignacio de la Llave
Ignacio de la Llave ✭
edited 06/13/23 inFormulas and Functions

Hello,

I have a project plan in Smartsheet with 2 columns:

  • % Complete
  • 相对魏ght of completion

This cells are used in parent rows (the phases of the project) and are used to indicate the completion of a phase but also the relative amount of effort that each phase has (as an average is not accurate enough)

I would like to have a cell in a parent to this rows to show the project completion. This is, multiply each row's "% complete" by the row's "Relative weight of completion" to get a total.

In Excel, I would use array formulas ({formula}) for this but, as far as I'm aware, this is not possible in Smartsheet, and I don't want to do it manually as the formula would need to be updated every time a row is added or removed.


Example:

image.png


So the goal would be that the "[Project]" goal would show:

=[% Complete]2*[Weight (% of Completion)]2+[% Complete]16*[Weight (% of Completion)]16+[% Complete]26*[Weight (% of Completion)]26+[% Complete]54*[Weight (% of Completion)]54+[% Complete]96*[Weight (% of Completion)]96+[% Complete]102*[Weight (% of Completion)]102


But also that, if I add any more children to the "[Project]" row, they would be automatically added to the calculation and I don't have to edit the formula manually (was thinking something that uses "CHILDREN()" but can't find any way).

Is it possible?

Thank you in advance!

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    I would suggest a helper column (can be hidden after setting up) that determines the weighted % on each parent row.

    =IF([Weight (% of Completion]@row <> "", [Weight (% of Completion]@row * [% Complete]@row)


    Then to add each piece together, you would use

    =SUM(CHILDREN([Helper Column]@row))

    10xViz.com

    · Share on Facebook Share on Twitter

Answers

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Try this - =\"We are at \" + [% closed rate]@row * 100 + \"% closed rate on ticket status for the month of \"+[Month]@row.<\/p>"},{"commentID":390301,"body":"

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), \"//www.santa-greenland.com/community/discussion/comment/\") + \"% 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":59,"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":"

\n \n https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula\n <\/a>\n<\/div>\n

It looks like you forgot to close out the AND <\/strong>funtion. Try this<\/p>

=IF([Invoice Received?]@row = 0, \"Green\", IF(AND([Renewal Date]@row >= TODAY(0), [Renewal Date]@row <= TODAY(+30)), \"Red\", \"Yellow\"))<\/code><\/p>

Hope this helps!<\/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":108832,"type":"question","name":"How do I create a IF(AND formula combining multiple cross references?","excerpt":"In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula: =COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback…","snippet":"In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting…","categoryID":322,"dateInserted":"2023-08-10T23:46:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:12:56+00:00","insertUserID":140084,"insertUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":140084,"lastUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":49,"score":null,"hot":3383486357,"url":"https:\/\/community.smartsheet.com\/discussion\/108832\/how-do-i-create-a-if-and-formula-combining-multiple-cross-references","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108832\/how-do-i-create-a-if-and-formula-combining-multiple-cross-references","format":"Rich","tagIDs":[207,219,254,344,440],"lastPost":{"discussionID":108832,"commentID":390262,"name":"Re: How do I create a IF(AND formula combining multiple cross references?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390262#Comment_390262","dateInserted":"2023-08-11T17:12:56+00:00","insertUserID":140084,"insertUser":{"userID":140084,"name":"Krystal Garcia","url":"https:\/\/community.smartsheet.com\/profile\/Krystal%20Garcia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hEDXYe8sIOY!ZF9XGSNgaPQ!0Bh2ICPdBzl","dateLastActive":"2023-08-11T17:23:36+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\/9UG82HSGCS9G\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:23:34+00:00","dateAnswered":"2023-08-11T00:00:03+00:00","acceptedAnswers":[{"commentID":390143,"body":"

I think I am following what you are attempting to do. Try this:<\/p>

COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), {Dispute & Chargeback Intake Sheet Type of CB}, \"AMEX\")<\/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":207,"urlcode":"Sales","name":"Sales"},{"tagID":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":344,"urlcode":"it-operations","name":"IT & Operations"},{"tagID":440,"urlcode":"project-management","name":"Project Management"}]}],"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

  • Is there a formula to show as %

    What I am trying to do is have my overview brought in automatically each month so it doesn't have to manually be typed in. The struggle I'm having is it wants to bring in as a decimal instead of a %. Below is the formula I am currently using. Is there a way to make it show as % ="We are at " + [% closed rate]@row + "…

  • IF/AND Formula

    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…

  • How do I create a IF(AND formula combining multiple cross references?

    In this screenshot I am sharing my rollup data sheet. Of the 6 Total Amex CB Count, I am trying to determine how many of those each of our Branches has but I keep getting different errors using formula: =COUNTIFS({Dispute & Chargeback intake sheet branch}, CONTAINS([Data Points]32, @cell), IF(AND({Dispute & Chargeback…