#UNPARSEABLE When Creating =AVERAGE(COLLECT Formula
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. ?)
Best Answer
-
There is no AVERAGE function. Try replacing it with AVG.
Answers
-
Try changing the average to a join with a comma as a delimiter and see what is being pushed to the average from the collect
for the second one its the same formula with a small adjustment. replace "Currently Employed" with
not(@cell = "Currently Employed")
-
@L_123 I am still very new to Smartsheet, can you give me an example of what the formula you suggested should look like?
-
=AVERAGE(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))
changes to
=JOIN(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"),", ")
for the second one its the same formula with a small adjustment. replace "Currently Employed" with
not(@cell = "Currently Employed")
-
@L_123 Using your 1st Join example results in: ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN, ADMIN
So, it's not collecting the months employed and then averaging, it is instead listing all currently employed where DivAtTerm is ADMIN.
-
Right, so that tells us that your issue is in the return column for the collect.
Check out your {MonthsEmp} reference and make sure it's pointing to the correct data
-
@L_123 You were correct! My {MonthsEmp} reference was calling the wrong column. With the join and the comma delimiter, it now returns a list of all of the # of months employed for current employees in the Admin Division. For Example: 12, 2, 6, 54, etc.
My original formula below (with the reference corrected) still returns #UNPARSEABLE:
=AVERAGE(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))
-
Make sure that your criterion range type for {TermYear} and {DivAtTerm} is text/number. If you are comparing date with text then it will error.
-
I just double checked the sheet being referenced and all requested columns are column type text/number...
-
There is no AVERAGE function. Try replacing it with AVG.
-
@Paul Newcome YES! Thank you, this was the issue!
How do I add multiple cell values?
This is the formula that works:
=AVG(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))
Instead of TermYear, I want to use my {SeparationType} column with the values "Voluntary" and "Nonvoluntary".
-
=AVG(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))
*lol i had this open in a different window, didn't see paul already got to it.
-
You would use the same syntax. Just adjust that particular range to the new range and adjust the criteria.
-
@L_123 I do that all the time. Hahaha
Help Article Resources
Categories
Check out the Formula Handbook template!
