#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_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"))

    · 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-31T15:58:11+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

想实践工作直接与公式Smartsheet?

Check out the公式手册模板!

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

 Watch video
=[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"}]},{"discussionID":108271,"type":"question","name":"Conditional formula - if a date is less than 30 days from today","excerpt":"I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula. =IF(AND([Event Date] > TODAY(), [Event Date] <= TODAY() + 30), \"Less than 30 days from today\", \"More than 30 days from today\") Help?","snippet":"I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula.…","categoryID":322,"dateInserted":"2023-07-28T18:21:58+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T19:22:21+00:00","insertUserID":159884,"insertUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":159884,"lastUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":31,"score":null,"hot":3381143059,"url":"https:\/\/community.smartsheet.com\/discussion\/108271\/conditional-formula-if-a-date-is-less-than-30-days-from-today","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108271\/conditional-formula-if-a-date-is-less-than-30-days-from-today","format":"Rich","lastPost":{"discussionID":108271,"commentID":387894,"name":"Re: Conditional formula - if a date is less than 30 days from today","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387894#Comment_387894","dateInserted":"2023-07-28T19:22:21+00:00","insertUserID":159884,"insertUser":{"userID":159884,"name":"Connie Cochran","url":"https:\/\/community.smartsheet.com\/profile\/Connie%20Cochran","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T19:21:59+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T19:21:56+00:00","dateAnswered":"2023-07-28T18:55:42+00:00","acceptedAnswers":[{"commentID":387890,"body":"

Try this:<\/p>

=IF(ISDATE([Event Date]@row), IF(AND([Event Date]@row > TODAY(), [Event Date]@row <= TODAY(30)), \"Less than 30 days from today\", \"More than 30 days from today\"), \"//www.santa-greenland.com/community/discussion/comment/\")<\/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":108267,"type":"question","name":"Combining IF Formula for Blank\/ Not Blank Cells","excerpt":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected Date\" null -Pending: Based on \"Anticipated Collcted Date\" not null and \"Collected Date\" null Below is what I have, but it's unparseable:…","snippet":"I want to create a formula that provides the below statuses: -Complete: Based on \"Collected Date\" not null -Incomplete: Based on \"Collected Date\" null and \"Antcipated Collected…","categoryID":322,"dateInserted":"2023-07-28T17:23:40+00:00","dateUpdated":null,"dateLastComment":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164288,"lastUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":48,"score":null,"hot":3381135147,"url":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108267\/combining-if-formula-for-blank-not-blank-cells","format":"Rich","lastPost":{"discussionID":108267,"commentID":387885,"name":"Re: Combining IF Formula for Blank\/ Not Blank Cells","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/387885#Comment_387885","dateInserted":"2023-07-28T18:28:47+00:00","insertUserID":164288,"insertUser":{"userID":164288,"name":"brownrobe","url":"https:\/\/community.smartsheet.com\/profile\/brownrobe","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-28T18:42:06+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,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-07-28T18:30:11+00:00","dateAnswered":"2023-07-28T18:22:11+00:00","acceptedAnswers":[{"commentID":387882,"body":"

Greetings @brownrobe<\/a>,<\/p>

Here is a possible solution for your formula:<\/p>

=IF(NOT(ISBLANK([Recovery Collected Date]@row)), \"Complete\", IF(AND(ISBLANK([Recovery Collected Date]@row), ISBLANK([Anticipated Collection Date]@row)), \"Incomplete\", IF(AND(NOT(ISBLANK([Anticipated Collection Date]@row)), ISBLANK([Recovery Collected Date]@row)), \"Pending\", \"//www.santa-greenland.com/community/discussion/comment/\")))<\/p>

Please confirm I have your column names correct and adjust as needed.<\/p>

I hope this helps, and have a great weekend.<\/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":[]}],"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

  • How do you calculate the average number of days between a submitted date and a start date?

    一个dditional 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…

  • Conditional formula - if a date is less than 30 days from today

    I'm trying to create a conditional formula to flag if less than 30 days from today. I create a separate column to calculate but I am getting an error on the formula. =IF(AND([Event Date] > TODAY(), [Event Date] <= TODAY() + 30), "Less than 30 days from today", "More than 30 days from today") Help?

  • Combining IF Formula for Blank/ Not Blank Cells

    I want to create a formula that provides the below statuses: -Complete: Based on "Collected Date" not null -Incomplete: Based on "Collected Date" null and "Antcipated Collected Date" null -Pending: Based on "Anticipated Collcted Date" not null and "Collected Date" null Below is what I have, but it's unparseable:…