I'm trying to get a formula for a sheet summary value to calculate average duration of rows where Risk Type = "EDAM Risk" and Status = "Resolved - Mitigated" or "Resolved - Decision."
Assuming:
Duration is in a column named Duration
Risk Type is in a column named Type
Status is in a column named Status
I've been using the built in AI feature to try to create this formula, but I keep getting either #UNPARSEABLE or #INVALID DATA TYPE when I use the formulas it is generating for me. Here's one example below that's giving me an #INVALID DATA TYPE error:
=AVG(COLLECT(Duration:Duration, Type:Type, "EDAM Risk", Status:Status, "Resolved - Mitigated"))
I'm not sure if something is broken with the formula, or if the problem is that my Duration column is #UNVALID DATA TYPE for any of my risks that are in active status (not the resolved statuses I've listed above). The Duration is column formula is:
=NETWORKDAYS([Date Identified]@row , [Completed Date]@row )
