Array formulas for project completion in Smartsheet

Ignacio de la Llave

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!

Paul Newcome

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

Paul Newcome

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

Ignacio de la Llave

Thank you Paul! Very useful info

Paul Newcome

Happy to help. 👍️