#UNPARSEABLE When Creating =AVERAGE(COLLECT Formula

LFaraco · 05/13/22

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. ?)

Paul Newcome · 05/19/22

There is no AVERAGE function. Try replacing it with AVG.

L_123 · 05/13/22

试着改变平均加入用逗号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")

LFaraco · 05/13/22

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<\/p>","bodyRaw":"[{\"insert\":\"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\\n\"}]","format":"rich","dateInserted":"2022-05-13T18:27:02+00:00","insertUser":{"userID":16866,"name":"L_123","title":"","url":"https:\/\/community.smartsheet.com\/profile\/L_123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T21:15:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/329604#Comment_329604","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/329604#Comment_329604

@L_123I am still very new to Smartsheet, can you give me an example of what the formula you suggested should look like?

L_123 · 05/13/22

=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")

LFaraco · 05/13/22

=AVERAGE(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))<\/p>

changes to <\/p>

=JOIN(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"),", ")<\/p>

for the second one its the same formula with a small adjustment. replace "Currently Employed" with<\/p>

not(@cell = "Currently Employed")<\/p>","bodyRaw":"[{\"insert\":\"=AVERAGE(COLLECT({MonthsEmp}, {TermYear}, \\\"CURRENTLY EMPLOYED\\\", {DivAtTerm}, \\\"ADMIN\\\"))\\nchanges to \\n=JOIN(COLLECT({MonthsEmp}, {TermYear}, \\\"CURRENTLY EMPLOYED\\\", {DivAtTerm}, \\\"ADMIN\\\"),\\\", \\\")\\n\\nfor the second one its the same formula with a small adjustment. replace \\\"Currently Employed\\\" with\\nnot(@cell = \\\"Currently Employed\\\")\\n\"}]","format":"rich","dateInserted":"2022-05-13T18:49:27+00:00","insertUser":{"userID":16866,"name":"L_123","title":"","url":"https:\/\/community.smartsheet.com\/profile\/L_123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T21:15:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/329609#Comment_329609","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/329609#Comment_329609

@L_123Using 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.

L_123 · 05/13/22

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

LFaraco · 05/19/22

Right, so that tells us that your issue is in the return column for the collect.<\/p>

Check out your {MonthsEmp} reference and make sure it's pointing to the correct data<\/p>","bodyRaw":"[{\"insert\":\"Right, so that tells us that your issue is in the return column for the collect.\\nCheck out your {MonthsEmp} reference and make sure it's pointing to the correct data\\n\"}]","format":"rich","dateInserted":"2022-05-13T19:06:03+00:00","insertUser":{"userID":16866,"name":"L_123","title":"","url":"https:\/\/community.smartsheet.com\/profile\/L_123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-22T21:15:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/329612#Comment_329612","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/329612#Comment_329612

@L_123You 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"))

Sameer Karkhanis · 05/19/22

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.

LFaraco · 05/19/22

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.<\/p>","bodyRaw":"[{\"insert\":\"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.\\n\"}]","format":"rich","dateInserted":"2022-05-19T18:32:06+00:00","insertUser":{"userID":95091,"name":"Sameer Karkhanis","url":"https:\/\/community.smartsheet.com\/profile\/Sameer%20Karkhanis","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Gojd36hb2yQ!xWjdseh3KiQ!Pg1PywniI56","dateLastActive":"2022-10-07T00:55:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/330345#Comment_330345","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/330345#Comment_330345

I just double checked the sheet being referenced and all requested columns are column type text/number...

Paul Newcome · 05/19/22

There is no AVERAGE function. Try replacing it with AVG.

LFaraco · 05/19/22

There is no AVERAGE function. Try replacing it with AVG.<\/p>","bodyRaw":"[{\"insert\":\"There is no AVERAGE function. Try replacing it with AVG.\\n\"}]","format":"rich","dateInserted":"2022-05-19T19:12:12+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-25T17:46:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/330363#Comment_330363","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/330363#Comment_330363

@Paul NewcomeYES! 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".

L_123 · 05/19/22

=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.

Paul Newcome · 05/19/22

You would use the same syntax. Just adjust that particular range to the new range and adjust the criteria.

Paul Newcome · 05/19/22

@L_123I do that all the time. Hahaha

#UNPARSEABLE When Creating =AVERAGE(COLLECT Formula

Best Answer

Answers

Help Article Resources

Categories