How to calculate % Planned in Father Rows?

Sergio Alvarado

Hi Community

I created a new column to calculate the planned percentage in my project; however, in the father rows is not calculating correctly, even if I tied the percentage between complete and Planned, the percentage is not the same.

I am using the following formula

=IFERROR(IF(Finalizar@row = Inicio@row , IF(TODAY() >= Inicio@row , 100, 0), ROUND(MAX(0, MIN(1, (TODAY() - Inicio@row ) / (Finalizar@row - Inicio@row ))) * 100, 0) + "%"), "")

Thanks for any comments or support.

jmyzk_cloudsmart_jp

Hi @Sergio Alvarado

Here is a method to calculate Planned% for both child rows and father (parent) rows, including special handling for 1-day tasks where Inicio = Finalizar.

https://app.smartsheet.com/b/publish?EQBCT=c533f94072f441af9a3924d4dcc6f34a

Column meanings:

• Inicio = Start date
• Finalizar = Finish date
• Father row = Parent row
• Duration = Finalizar – Inicio

Identify parent rows:

[isParent] = COUNT(CHILDREN()) > 1

Planned% AVG (simple average roll-up):

[Planned% AVG] =

IF(
  AND(ISDATE(Inicio@row), ISDATE(Finalizar@row)),


     IF(

        isParent@row,

        AVG(CHILDREN()),

        IF(

           Finalizar@row = Inicio@row,

              IF(TODAY() >= Inicio@row, 1, 0),

              MIN(1, MAX(0, (TODAY() - Inicio@row) / (Finalizar@row - Inicio@row)))

        )

     ),

""

)

How it works:

• If the row is a parent, it returns the average of the children.
• If it is a 1-day task, Planned% is 1 (100%) once TODAY reaches the date.
• Otherwise, it calculates % based on elapsed time between Inicio and Finalizar, clamped between 0 and 1.

Planned% AVGW (duration-weighted roll-up):

[Planned% AVGW] =

IF(

  AND(ISDATE(Inicio@row), ISDATE(Finalizar@row)),

     IF(

        isParent@row,

        AVGW(CHILDREN(), CHILDREN(Duration@row)),

        IF(

           Finalizar@row = Inicio@row,

              IF(TODAY() >= Inicio@row, 1, 0),

              MIN(1, MAX(0, (TODAY() - Inicio@row) / (Finalizar@row - Inicio@row)))

        )

     ),

""

)

How it works:

• Parent rows roll up Planned% based on duration-weighted child progress, similar to MS Project.
• Child rows calculate Planned% the same way as in the AVG version.

Note

Why we use AND(ISDATE(Inicio@row ), ISDATE(Finalizar@row )) instead of IFERROR:
Using ISDATE prevents errors before they occur rather than catching them afterward.
This avoids issues when parent rows, header rows, or incomplete tasks have blank dates.
It ensures the formula only runs when both dates are valid, keeping the sheet clean with no error values.

Why we use Smartsheet’s % column format (and do not use +"%"):
The formulas return a value between 0 and 1, and the % column format automatically displays them as 0%–100%.
If we converted values to text (like “75%”), then AVG(CHILDREN()) and AVGW() would break, because text cannot be averaged.
Using numeric % values also prevents divide-by-zero errors in averaging and keeps parent roll-ups working correctly.

Optional helper for 1-day tasks:

[TODAY() >= Inicio@row and Duration  = 1] =AND(TODAY() >= Inicio@row , Duration@row  = 1)

The Predecessors column is optional, depending on your project logic.