Array formulas for project completion in Smartsheet
Hello,
I have a project plan in Smartsheet with 2 columns:
 % Complete
 Relative weight 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:
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!
Best 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))
Answers

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))

Thank you Paul! Very useful info

Happy to help. 👍️
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.5K Get Help
 61 Global Discussions
 46 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!