I am trying to pull an average of months employed for current employees in a specific division. My formula is:
=AVERAGE(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))
{MonthsEmp} is a column formula:
=ROUND((IF(ISDATE([Term Date]@row), IF(NETDAYS([Term Date]@row, TODAY()) > 0, NETDAYS([Hire Date]@row, [Term Date]@row)), NETDAYS([Hire Date]@row, TODAY())) / 365) * 12)
{TermYear} is a column formula:
=IF(ISBLANK([Term Date]@row), "Currently Employed", YEAR([Term Date]@row))
{DivAtTerm} is a text/Number column type
I'm getting the #UNPARSEABLE error for the Average(Collect formula and I cannot figure out why. I am still very new to Smartsheet.
Once I get this formula to work, I'll need the same formula but for employees where {TermYear} is not "CURRENTLY EMPLOYED".
I appreciate any help or ideas that anyone can provide!
(Edit to add: My original sheet with the column formulas above works just fine, I do not know if the issue with the Collect formula is due to the fact that the sheet reference is for columns with a column formulas. ?)
