#UNPARSEABLE当创建=AVERAGE(收集公式

LFaraco
LFaraco ✭
编辑05/13/22 在公式与函数

我试图拉一个特定部门的当前员工的平均受雇月。我的公式是:

=AVERAGE(COLLECT({MonthsEmp}， {TermYear}， "当前受雇"，{DivAtTerm}， "ADMIN"))

{MonthsEmp}是一个列公式:

=ROUND(IF(ISDATE([任期日期]@行)，IF(NETDAYS([任期日期]@行，今天())> 0,NETDAYS([聘用日期]@行，[任期日期]@行))，NETDAYS([聘用日期]@行，今天()))/ 365)* 12)

{TermYear}是一个列公式:

=IF(ISBLANK([任期日期]@row)， "目前受雇"，年份([任期日期]@row))

{DivAtTerm}是一个文本/数字列类型

我得到#UNPARSEABLE错误的平均值(收集公式，我不知道为什么。我对Smartsheet还是个新手。

一旦我让这个公式工作，我将需要相同的公式，但对于{TermYear}不是“当前雇用”的员工。

感谢任何人提供的任何帮助或想法!

(编辑添加:我的原始表与上面的列公式工作得很好，我不知道收集公式的问题是否由于表引用是针对列公式的事实。？)

· 在Facebook上分享 在推特上分享

最佳答案

答案

  • L_123
    L_123 ✭✭✭✭✭✭
    编辑05/13/22

    尝试将平均值更改为以逗号作为分隔符的连接，并查看从集合中推入平均值的内容

    第二个是同样的公式，只是稍作调整。将“目前在职”替换为

    not(@cell = "当前受雇")

    · 在Facebook上分享 在推特上分享
  • 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-04-14T19:35:12+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_123我对Smartsheet还很陌生，你能给我一个你建议的公式应该是什么样的例子吗?

    · 在Facebook上分享 在推特上分享
  • L_123
    L_123 ✭✭✭✭✭✭

    =AVERAGE(COLLECT({MonthsEmp}， {TermYear}， "当前受雇"，{DivAtTerm}， "ADMIN"))

    更改

    =JOIN(COLLECT({MonthsEmp}， {TermYear}， "当前受雇"，{DivAtTerm}， "ADMIN")，"， ")


    第二个是同样的公式，只是稍作调整。将“目前在职”替换为

    not(@cell = "当前受雇")

    · 在Facebook上分享 在推特上分享
  • 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-04-14T19:35:12+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_123使用您加入例子结果1:管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员,管理员

    因此，它不是收集受雇的月数然后取平均值，而是列出所有当前受雇的月数，其中DivAtTerm为ADMIN。

    · 在Facebook上分享 在推特上分享
  • L_123
    L_123 ✭✭✭✭✭✭

    好的，这告诉我们你的问题在收集的返回列中。

    检查{MonthsEmp}引用，确保它指向正确的数据

    · 在Facebook上分享 在推特上分享
  • 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-04-14T19:35:12+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}引用调用了错误的列。使用连接和逗号分隔符，它现在返回Admin Division中当前员工的所有# of months的列表。例如:12,2,6,54等等。

    我原来的公式(参考更正)仍然返回#UNPARSEABLE:

    =AVERAGE(COLLECT({MonthsEmp}， {TermYear}， "当前受雇"，{DivAtTerm}， "ADMIN"))

    · 在Facebook上分享 在推特上分享
  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭

    确保{TermYear}和{DivAtTerm}的标准范围类型是text/number。如果你是比较日期和文本，那么它会出错。

    · 在Facebook上分享 在推特上分享
  • 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

    我刚刚仔细检查了表被引用和所有请求的列是列类型的文本/数字…

    · 在Facebook上分享 在推特上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭
    ✓回答

    没有AVERAGE函数。试着用AVG代替它。

    thinkspi.com

    · 在Facebook上分享 在推特上分享
  • 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-04-14T19:43:03+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新来的是的!谢谢，这就是问题所在!

    如何添加多个单元格值?

    这是一个有效的公式:

    =AVG(COLLECT({MonthsEmp}， {TermYear}， "当前受雇"，{DivAtTerm}， "ADMIN"))

    而不是TermYear，我想使用我的{SeparationType}列的值“Voluntary”和“Nonvoluntary”。

    · 在Facebook上分享 在推特上分享
  • L_123
    L_123 ✭✭✭✭✭✭
    编辑05/19/22

    =AVG(COLLECT({MonthsEmp}， {TermYear}， "当前受雇"，{DivAtTerm}， "ADMIN"))


    哈哈，我在另一个窗口打开了这个，没有看到保罗已经打开了。

    · 在Facebook上分享 在推特上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    您将使用相同的语法。只要将特定的范围调整到新的范围并调整标准。

    thinkspi.com

    · 在Facebook上分享 在推特上分享
  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    @L_123我一直都这么做。哈哈哈

    thinkspi.com

    · 在Facebook上分享 在推特上分享

帮助文章资源欧宝体育app官方888

在Smartsheet中创建和编辑公式

交叉表参考的公式组合

Smartsheet功能列表

类别

想直接在Smartsheet中练习使用公式吗?

请查看公式手册模板!
Hi @ovelzone<\/a> <\/p>

I hope you're well and safe!<\/p>

Have you explored using a Report instead?<\/p>

Would that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic weekend!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"},{"commentID":371801,"body":"

Hello @ovelzone<\/a> - Give this a try.<\/p>

=SUMIF(Name:Name, Names@row, Scores:Scores)<\/code><\/p>

I hope that helps!<\/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":104028,"type":"question","name":"IF Then [Incorrect Argument] Question.","excerpt":"Please assist with this formula: =IF(Status@row, \"Fully Approved\", [Signing Status]@row, \"Sent\", NETDAYS([Fully Approved Date]@row + 1)) In this case the bold portion is my THEN calculation.","categoryID":322,"dateInserted":"2023-04-14T13:00:30+00:00","dateUpdated":null,"dateLastComment":"2023-04-14T14:13:09+00:00","insertUserID":148602,"insertUser":{"userID":148602,"name":"Mike B","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20B","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/AT4NBT0RCEB5\/nMYTT0CUGWMWH.jpg","dateLastActive":"2023-04-14T14:11:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":148602,"lastUser":{"userID":148602,"name":"Mike B","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20B","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/AT4NBT0RCEB5\/nMYTT0CUGWMWH.jpg","dateLastActive":"2023-04-14T14:11:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":37,"score":null,"hot":3362963019,"url":"https:\/\/community.smartsheet.com\/discussion\/104028\/if-then-incorrect-argument-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/104028\/if-then-incorrect-argument-question","format":"Rich","lastPost":{"discussionID":104028,"commentID":371797,"name":"Re: IF Then [Incorrect Argument] Question.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/371797#Comment_371797","dateInserted":"2023-04-14T14:13:09+00:00","insertUserID":148602,"insertUser":{"userID":148602,"name":"Mike B","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Mike%20B","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/AT4NBT0RCEB5\/nMYTT0CUGWMWH.jpg","dateLastActive":"2023-04-14T14:11:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-04-14T14:13:25+00:00","dateAnswered":"2023-04-14T14:04:45+00:00","acceptedAnswers":[{"commentID":371793,"body":"

Thanks for the clarification, @Mike B<\/a> . Give this one a go. Let me know if this works!<\/p>

=IF(AND(Status@row = \"Fully Approved\", [Signing Status]@row = \"Sent\"), NETDAYS([Fully Approved Date]@row, TODAY(), \"//www.santa-greenland.com/community/discussion/91114/\")<\/code><\/p>"},{"commentID":371796,"body":"

OOPS! I forgot a parenthesis. Now try, @Mike B<\/a> .<\/p>

=IF(AND(Status@row = \"Fully Approved\", [Signing Status]@row = \"Sent\"), NETDAYS([Fully Approved Date]@row, TODAY()), \"//www.santa-greenland.com/community/discussion/91114/\")<\/code><\/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":104023,"type":"question","name":"Summary Task formula for COUNTIF Before TODAY and Status IS NOT \"Not Started\"","excerpt":"Hello, I'm looking for a summary task formula to count a row if the date is before today and Status value is NOT \"Location Not Started\". I've tried a few formulas and can't get to a working one. This is the last one I tried and getting and \"Incorrect Argument\" error message: =COUNTIFS([Confirmed Install Date]:[Confirmed…","categoryID":322,"dateInserted":"2023-04-14T03:19:46+00:00","dateUpdated":null,"dateLastComment":"2023-04-14T16:57:56+00:00","insertUserID":119439,"insertUser":{"userID":119439,"name":"Lisa Matthews","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Matthews","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!GlFVn_Nz2Xw!wlDUP2yDueY!ag-sVUwuD_X","dateLastActive":"2023-04-14T16:55:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":119439,"lastUser":{"userID":119439,"name":"Lisa Matthews","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Matthews","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!GlFVn_Nz2Xw!wlDUP2yDueY!ag-sVUwuD_X","dateLastActive":"2023-04-14T16:55:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":45,"score":null,"hot":3362936262,"url":"https:\/\/community.smartsheet.com\/discussion\/104023\/summary-task-formula-for-countif-before-today-and-status-is-not-not-started","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/104023\/summary-task-formula-for-countif-before-today-and-status-is-not-not-started","format":"Rich","lastPost":{"discussionID":104023,"commentID":371835,"name":"Re: Summary Task formula for COUNTIF Before TODAY and Status IS NOT \"Not Started\"","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/371835#Comment_371835","dateInserted":"2023-04-14T16:57:56+00:00","insertUserID":119439,"insertUser":{"userID":119439,"name":"Lisa Matthews","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Lisa%20Matthews","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!GlFVn_Nz2Xw!wlDUP2yDueY!ag-sVUwuD_X","dateLastActive":"2023-04-14T16:55:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"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\/SVIY6BLICVGF\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-04-14T16:58:02+00:00","dateAnswered":"2023-04-14T15:24:02+00:00","acceptedAnswers":[{"commentID":371811,"body":"

@Lisa Matthews<\/a> your formula is close but I tweaked it a little, should work now.<\/p>

=COUNTIFS([Confirmed Install Date]:[Confirmed Install Date], <=TODAY(), [Combined Status]:[Combined Status], NOT(@cell = \"Location Not Started\"))<\/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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">