Seeing #UNPARSEABLE issue when applying formulae in the summary

Kiranb

Wanted to calculate the Average of time difference with some conditions like Implemented column is checked for the Level 1, and the final Level2 task is completed, then calculate time from the dates in the final level2 task called 'ECO Implementation".

Here is the formula I generated but I see #UNPARSEABLE error.

=AVG(COLLECT([Actual Completion Date]:[Actual Completion Date], AND(ANCESTORS([Level]@row) = 1, [Implemented]@column= true, [Task]@row = "ECO Implementation", [Status]@row = "Completed")) - COLLECT([Start Date]:[Start Date], AND(ANCESTORS([Level]@row) = 1, [Implemented]@column= true, [Task]@row = "ECO Implementation", [Status]@row = "Completed")))

Request your guidance to correct the mistake that I couldn't find.
Also, as a next step, I'd like to calculate AVG time based on one more condition called Workflow type that selected for all Level1 tasks. Need your support!

Jennifer Kurtz

Hi @Kiranb -

The syntax for the COLLECT function is (Range, Criterion_Range1, Criterion1, Criterion_Range2, Criterion 2…..)

I suspect you may not need your AND statements, as they look like various criteria defining what you want to collect. It looks like your formula may be missing the criterion ranges.

Feel free to include a screenshot of your sheet(s) if you're able to do so! That's often really helpful for the community to troubleshoot formulas. :)

Kindly,

jen