AVG(COLLECT)配方-需要帮助
这是我的工作，但这不是我想要的，因为它只适用于一个完全匹配的日期。
=AVG(COLLECT([Column5]1:[Column5]4， [Column7]1:[Column7]4, DATE(2022, 10,4))))
第7列是一个DATE列，只有当日期范围在10月1日到10月31日之间时，我才想对第5列中的数字求平均值。
它适用于单个日期，但我不知道为什么我不能输入日期范围。我试过各种方法，但都没用。
谢谢!
答案
-
她名叫Stara ✭✭✭✭✭✭我希望你一切安好!
试试这样做。
=IFERROR(AVG(COLLECT([Column5]1:[Column5]4， [Column7]1:[Column7]4, AND(@cell >= DATE(2022, 10,1)， @cell <= DATE(2022, 10,31))))， ""))
那有用吗?
我希望这对你有帮助!
注意安全，周末愉快!
最好的
✅我的帖子是否帮助或回答了你的问题或解决了你的问题?请支持社区将其标记为“有见地”/“投票赞成”、“很棒”或“并且”作为公认的答案。这将使其他人更容易找到解决方案或帮助回答!
智能表专家顾问兼合伙人
W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35
请随时与我联系，了解有关Smartsheet，集成，一般工作流建议或其他方面的帮助。
-
AB200001 ✭我用它得到了“不可解析”——我已经尝试了几十次AVG(COLLECT)，但没有成功，所以我切换到“AVERAGEIF”，这似乎有效。
这是我根据你的建议制定的公式。我希望我能弄清楚为什么它不起作用。这两个字段是“日期”和“数字/文本”。
=IFERROR(AVG(COLLECT([总分无公式]1:[总分无公式]10)，[日期得分]1:[日期得分]10,AND(@cell >= Date (2022, 10,1)， @cell <= Date (2022, 10,31))))， ""))
-
吉纳维芙P。 员工管理看起来您过早地关闭了COLLECT函数。把COLLECT想象成一个过滤器……首先列出要过滤的列或范围，然后在其后面列出每个列和条件。
我去掉了中间的括号，加粗的部分:
=IFERROR(AVG(COLLECT([总分无公式]1:[总分无公式]10，[日期得分]1(日期得分):10,(@cell > =日期(2022年10 1),@cell < =日期(2022年10,31))))" ")
我还会注意到，在这个函数中，您将范围限制为10行，这是您想要做的吗?如果要搜索整个列，请删除列名后的行号:
=IFERROR(AVG(COLLECT([总分无公式]:[总分无公式])，[日期]得分:[日期]得分,(@cell > =日期(2022年10 1),@cell < =日期(2022年10,31))))" ")
欢呼,
吉纳维芙
-
AB200001 ✭
这说得通，但我还是变得不可思议。我也不知道为什么。谢谢你的努力!
-
吉纳维芙P。 员工管理
帮助文章参考资料欧宝体育app官方888
类别
Try this<\/p>
=IF(Available@row > 0, \"PES\", JOIN(INDEX(COLLECT(Facility:Facility, [Microscope: 1]:[Microscope: 1], HAS(@cell,\"6633112443\")), 1)))<\/p>
Will this work for you?<\/p>
Kelly<\/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":106579,"type":"question","name":"Split Text","excerpt":"Can someone assist me? I need a formula on how to split the text for this below. Ex. LOW - VS-87 - Service-FS Battery Replacement Lead-MEGA MALL-1259410 I need a to have a column that would write MEGA MALL only.","categoryID":322,"dateInserted":"2023-06-17T17:04:55+00:00","dateUpdated":null,"dateLastComment":"2023-06-18T01:39:56+00:00","insertUserID":103391,"insertUser":{"userID":103391,"name":"Marilen.Navarro103391","url":"https:\/\/community.smartsheet.com\/profile\/Marilen.Navarro103391","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-09\/60\/88\/n6088a8f5166f388fd95cfcb413bcbd0e.jpg","dateLastActive":"2023-06-18T01:41:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":112221,"lastUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-06-19T02:46:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3374075691,"url":"https:\/\/community.smartsheet.com\/discussion\/106579\/split-text","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106579\/split-text","format":"Rich","lastPost":{"discussionID":106579,"commentID":381096,"name":"Re: Split Text","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381096#Comment_381096","dateInserted":"2023-06-18T01:39:56+00:00","insertUserID":112221,"insertUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2023-06-19T02:46:11+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-06-18T01:42:10+00:00","dateAnswered":"2023-06-18T01:39:56+00:00","acceptedAnswers":[{"commentID":381096,"body":"
