多个标准的平均值

PaigeKucz ✭

04/11/23 在公式和函数

我期待着看看是否有一种方法，有多个平均if来显示当前工作项目的视觉效果

因此，如果状态=工作，并分配给第4组，我希望显示平均天数:

1.）提交分诊

2)。分诊开始

3)。找出根本原因

4)。委员会批准的根本原因

5)。市议会批准试行计划

标签:

0 ·

最佳答案

保罗新来的 ✭✭✭✭✭✭

04/11/23 ✓回答

这听起来像是你实际上需要一个AVG/COLLECT组合，而不是能够包含多个范围/标准集。

=AVG(COLLECT([天数]:[天数]，状态:状态，@cell = "Working"，组:组，@cell = "Group 4")))

thinkspi.com

0 · 在Facebook上分享在Twitter上分享

答案

保罗新来的 ✭✭✭✭✭✭

04/11/23 ✓回答

这听起来像是你实际上需要一个AVG/COLLECT组合，而不是能够包含多个范围/标准集。

=AVG(COLLECT([天数]:[天数]，状态:状态，@cell = "Working"，组:组，@cell = "Group 4")))

thinkspi.com

0 · 在Facebook上分享在Twitter上分享

帮助文章参考资料欧宝体育app官方888

在智能表中创建和编辑公式

交叉表引用的公式组合

智能表功能列表

类别

想要直接在智能表中练习使用公式吗?

请查看公式手册模板!

30, {011-AP Archive -# of Days to Complete}, <60) I was able to do this same formula with a COUNTIFS but its' not working the same, what am I missing.","categoryID":322,"dateInserted":"2023-06-16T15:00:58+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":156010,"lastUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":26,"score":null,"hot":3373857824,"url":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106550\/invalid-operation","format":"Rich","lastPost":{"discussionID":106550,"commentID":381027,"name":"Re: Invalid Operation","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381027#Comment_381027","dateInserted":"2023-06-16T15:22:46+00:00","insertUserID":156010,"insertUser":{"userID":156010,"name":"AliT","url":"https:\/\/community.smartsheet.com\/profile\/AliT","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!80tRN2Ch-HQ!nDHdH1rxiPw!1_-nYVqJJzq","dateLastActive":"2023-06-16T15:22:32+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-16T15:22:31+00:00","dateAnswered":"2023-06-16T15:19:44+00:00","acceptedAnswers":[{"commentID":381026,"body":"

Try this:<\/p>

SUMIFS({011-AP Archive -# of Days to Complete}, {011-AP Archive -# of Days to Complete}, > 30, {011-AP Archive -# of Days to Complete}, < 60)<\/p>

With SUMIFS, you have to include the range to SUM separately from the ranges to compare. In your scenario, they are the same, but can be different.<\/p>

SUMIFS( range, criterion_range1, criterion1, [ criterion_range2, criterion2... ])<\/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":106528,"type":"question","name":"Formula Calculation with a zero value denominator","excerpt":"I'm trying to create a formula to calculate the # Hours per remaining week based off the total HOURS remaining. Calculation takes into consideration the calculated remaining # of weeks on a project. Logic should be: Take Remaining hours \/ # of remaining weeks. If remaining weeks is < 1, simply return the amount of…","categoryID":322,"dateInserted":"2023-06-15T20:54:01+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T16:47:02+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161931,"lastUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":20,"score":null,"hot":3373797663,"url":"https:\/\/community.smartsheet.com\/discussion\/106528\/formula-calculation-with-a-zero-value-denominator","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106528\/formula-calculation-with-a-zero-value-denominator","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106528,"commentID":381042,"name":"Re: Formula Calculation with a zero value denominator","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381042#Comment_381042","dateInserted":"2023-06-16T16:47:02+00:00","insertUserID":161931,"insertUser":{"userID":161931,"name":"Christine Cao","title":"Engagement Manager","url":"https:\/\/community.smartsheet.com\/profile\/Christine%20Cao","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-16T16:47:14+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\/09DN1EF6F1P9\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-16T16:47:11+00:00","dateAnswered":"2023-06-16T11:42:38+00:00","acceptedAnswers":[{"commentID":380991,"body":"

Hi @Christine Cao<\/a>,<\/p>

It's down to your formula and rounding. What's happening is that you have 0.3 of a week (which I'm guessing is actually 2 days - 0.2857..., rounded to 0.3). If you calculate 321.5\/(2\/7), you end up with the 1125.3 (to 1 dp).<\/p>

You can get round this by altering your formula to do what you're actually after:<\/p>

=IF([Column2]9 >= 1, [Column3]@row \/ [Column2]9, MAX([Column3]@row, 0))<\/p>

This should make it so if weeks remaining is equal to or greater than 1 it will do the calculation, and otherwise take the MAX of either 0 or column 3.<\/p>

Hi Mirelle,<\/p>

<\/p>

When applying the global update to modify the column, right under the selection of the column name, there are two checkboxes. One to change column property and the other is to change column cell data. Both are checked by default. To ensure only the new dropdown values are pushed and no existing values are updated, all you have to do it uncheck the box which says Change column cell data. This will remove the Pick value from list option altogether and you can apply the update.<\/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":[]}">