IFERROR or Similar Alternative

Platform discussions
,
1

While creating calculated columns on a team board, I continue to run into (quality of life) issues.

I am attempting to create a field that evaluates the % of Subitems Complete. Because formulas in the parent task cannot reference sub tasks without mirroring, I created a field that determines if the sub task is completed or not (Subitems Completed). This value brings in blank entries for tasks that contain no subtasks. To overcome this I have attempted to create conditional statements that set the value to 0 and none work on a rollup level. I have tried many variations of this formula with no luck. Any help would be appreciated.

IF(AND({Subitems Completed SubItems}>=0,{Subitems Completed SubItems}<= 100),(ROUND(DIVIDE({Subitems Completed SubItems},{Subitems#Count}),2) * 100),0)

The problem, is that even though the IF statement is supposed to set all other values to 0, it will not work when included in additional statements. Having a IFERROR, or a working IF statement, would make this SO much easier.

2

Hi Andrew,

Looks like a couple of syntax errors and also you’re sometimes dividing by 0. It’s much easier to work with more complicated formulas when you use indentation.

I’ve cleaned up the formula and clamped the subitem count so it’s always at least 1.

How is this?

IF(
    AND(
        {Subitems Completed SubItems}>=0,
        {Subitems Completed SubItems}<= 100
    ),
    ROUND(
        DIVIDE(
            {Subitems Completed SubItems},
            MAX(
                {Subitems#Count},
                1
            )
        ),
        2
    ) * 100,
    0
)

As an additional point, I’m not sure I fully understand your condition at the start of the formula. Perhaps it’s not necessary. Was that an attempt to make the formula comply? This might be sufficient:

ROUND(
    DIVIDE(
        {Subitems Completed SubItems},
        MAX(
            {Subitems#Count},
            1
        )
    ),
    2
) * 100
1 Like
3

Thank you! The 2nd formula works great.

The If statement was an attempt to make it comply, but no cigar.

1 Like