#UNPARSEABLE When Creating =AVERAGE(COLLECT Formula

LFaraco
LFaraco ✭
edited 05/13/22 inFormulas and Functions

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

· Share on Facebook Share on Twitter

Best Answer

一个nswers

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 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

    for the second one its the same formula with a small adjustment. replace "Currently Employed" with

    not(@cell = "Currently Employed")

    · Share on Facebook Share on Twitter
  • LFaraco
    LFaraco ✭
    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-07-31T12:54:52+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?

    · Share on Facebook Share on Twitter
  • L_123
    L_123 ✭✭✭✭✭✭

    =AVERAGE(COLLECT({MonthsEmp}, {TermYear}, "CURRENTLY EMPLOYED", {DivAtTerm}, "ADMIN"))

    更改

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

    · Share on Facebook Share on Twitter
  • LFaraco
    LFaraco ✭
    =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-07-31T12:54:52+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.

    · Share on Facebook Share on Twitter
  • L_123
    L_123 ✭✭✭✭✭✭

    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

    · Share on Facebook Share on Twitter
  • LFaraco
    LFaraco ✭
    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-07-31T12:54:52+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_123你是正确的!我的{MonthsEmp}引用卡尔ling 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"))

    · Share on Facebook Share on Twitter
  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    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.

    · Share on Facebook Share on Twitter
  • LFaraco
    LFaraco ✭
    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...

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    一个nswer ✓

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

    10xViz.com

    · Share on Facebook Share on Twitter
  • LFaraco
    LFaraco ✭
    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-07-31T19:57:04+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".

    · Share on Facebook Share on Twitter
  • L_123
    L_123 ✭✭✭✭✭✭
    edited 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.

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

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

    10xViz.com

    · Share on Facebook Share on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @L_123I do that all the time. Hahaha

    10xViz.com

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
@Kris Peeters<\/a> <\/p>

you should be able to use =Countifs([Al Javor]@row:[Lisa Young]@row,\"1 - High\")<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108307,"type":"question","name":"Formula to Average Performance Score for Various Service Categories","excerpt":"I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has \"civil engineer\" selected, I want a running formula that averages all the civil engineer ratings. I have tried using the =averageif() formula, but I continue to…","snippet":"I am trying to create a formula that averages the performance score of various service categories. For example, whenever the service category (drop down box) has \"civil engineer\"…","categoryID":322,"dateInserted":"2023-07-31T15:35:13+00:00","dateUpdated":"2023-07-31T15:48:17+00:00","dateLastComment":"2023-07-31T18:57:50+00:00","insertUserID":164346,"insertUser":{"userID":164346,"name":"ullkay95","url":"https:\/\/community.smartsheet.com\/profile\/ullkay95","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!bmtAmxLQVL0!e6DCx07vJ9c!25n9oP55COS","dateLastActive":"2023-07-31T18:59:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":164346,"lastUserID":45516,"lastUser":{"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-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":11,"countViews":31,"score":null,"hot":3381654183,"url":"https:\/\/community.smartsheet.com\/discussion\/108307\/formula-to-average-performance-score-for-various-service-categories","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108307\/formula-to-average-performance-score-for-various-service-categories","format":"Rich","lastPost":{"discussionID":108307,"commentID":388084,"name":"Re: Formula to Average Performance Score for Various Service Categories","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388084#Comment_388084","dateInserted":"2023-07-31T18:57:50+00:00","insertUserID":45516,"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-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/2HQ91LNQF3GG\/screenshot-2023-07-31-114610.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Screenshot 2023-07-31 114610.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-31T18:52:12+00:00","dateAnswered":"2023-07-31T18:48:05+00:00","acceptedAnswers":[{"commentID":388078,"body":"

In that case you would use the same syntax but you would reference the column in the sheet using the appropriate column name. <\/p>

[Column name]:[Column name]<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108278,"type":"question","name":"How do you calculate the average number of days between a submitted date and a start date?","excerpt":"Additional context: We use Smartsheet to track support requests from the organization. We have an automated column that records the day the form is filled out. The form also has a date range of when they need their request completed by. I would like to calculate the average amount of days people provide from the day they…","snippet":"Additional context: We use Smartsheet to track support requests from the organization. We have an automated column that records the day the form is filled out. The form also has a…","categoryID":322,"dateInserted":"2023-07-28T20:36:42+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T21:00:45+00:00","insertUserID":128090,"insertUser":{"userID":128090,"name":"Carlos Fernandez-Torres","url":"https:\/\/community.smartsheet.com\/profile\/Carlos%20Fernandez-Torres","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T21:30:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"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-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":40,"score":null,"hot":3381156447,"url":"https:\/\/community.smartsheet.com\/discussion\/108278\/how-do-you-calculate-the-average-number-of-days-between-a-submitted-date-and-a-start-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108278\/how-do-you-calculate-the-average-number-of-days-between-a-submitted-date-and-a-start-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108278,"commentID":387924,"name":"Re: How do you calculate the average number of days between a submitted date and a start date?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387924#Comment_387924","dateInserted":"2023-07-28T21:00:45+00:00","insertUserID":45516,"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-07-31T19:57:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/QO67SBLMOJQA\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T21:01:27+00:00","dateAnswered":"2023-07-28T21:00:45+00:00","acceptedAnswers":[{"commentID":387924,"body":"

=[End Date]@row - [Start Date]@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions