平均RGB球
我希望这是相当简单的,但我简单的头脑正在努力拼凑起来。
我有一个父行与多个孩子,但只有一个层次结构低于它,我想父行平均的红,绿和蓝球的状态对孩子行…有意义吗?
帮助将非常感激,屏幕截图如下:
最佳答案
-
Leibel博士年代 ✭✭✭✭✭✭
不需要添加任何单元格引用,只需按原样放入公式。
=如果(条件统计(儿童(),“绿色”)>条件统计(儿童(),“红色”),如果(条件统计(儿童(),“绿色”)>条件统计(儿童(),“蓝色”)、“绿色”、“蓝色”),如果(条件统计(儿童(),“红色”)>条件统计(儿童(),“蓝色”),“红”“蓝”))
答案
-
迈克Wilday ✭✭✭✭✭✭
我使用了一个辅助列和一些公式。
- 在符号列的右侧添加一个数字/文本列。我把我的书命名为《得分》
- 将以下公式添加到每个子行:=IF((电子邮件保护)= "绿色",1,IF((电子邮件保护)= "黄色",2,IF((电子邮件保护)= "红色",3,IF((电子邮件保护)= "Blue", 4))))用您的列名替换状态,如果您的名称有空格或以数字结尾,请使用括号[]。
- 在helper列中,为每个父行添加以下公式:=AVG(CHILDREN())这将提供子行的平均值。
- 然后在符号列的父行中向每个父行添加以下公式:=IF((电子邮件保护)< 2,“绿色”,IF((电子邮件保护)= <3, "黄色",IF((电子邮件保护)< 4, "红色",IF((电子邮件保护)>= 4, "Blue", "Blah"))))
这将为您提供基于颜色的平均分数。
我把它们分为绿色1,黄色2,红色3,蓝色4。
这对你有用吗?
-
Gunn_Jack ✭✭✭✭
谢谢你的回复
它在一定程度上是有用的,但不是我想要的。
我认为原因是它给了RYGB一个权重,在这个例子中,所有的权重都是相等的。也许平均是描述它的错误方式,我想我本质上想要的是让它告诉我哪种颜色出现得最多。
例如,在上面的截图中,有16个部分,如果9个是绿色的,父行应该显示为绿色。根据你的公式,如果我有12个绿色和4个蓝色,它的平均值是一个红色。(注意,我编辑了公式,使用了绿- 1,红- 2和蓝- 3):
=如果(和(得分[1]@row < 1.5,得分[1]@row > 1),“绿色”,如果(和(得分[1]@row < = 2.5,得分[1]@row > = 1.5),“红色”,如果(得分[1]@row > 2.5,“蓝色”," ")))
我应该继续用你的吗?
谢谢,
杰克
-
Leibel博士年代 ✭✭✭✭✭✭
下面是一个我认为对你有用的公式。
你可以切换颜色(记住它只适用于3种颜色)。此外,颜色的顺序也会影响领带返回的颜色……
=如果(条件统计(儿童(),“绿色”)>条件统计(儿童(),“红色”),如果(条件统计(儿童(),“绿色”)>条件统计(儿童(),“蓝色”)、“绿色”、“蓝色”),如果(条件统计(儿童(),“红色”)>条件统计(儿童(),“蓝色”),“红”“蓝”))
-
Gunn_Jack ✭✭✭✭
-
Leibel博士年代 ✭✭✭✭✭✭
不需要添加任何单元格引用,只需按原样放入公式。
=如果(条件统计(儿童(),“绿色”)>条件统计(儿童(),“红色”),如果(条件统计(儿童(),“绿色”)>条件统计(儿童(),“蓝色”)、“绿色”、“蓝色”),如果(条件统计(儿童(),“红色”)>条件统计(儿童(),“蓝色”),“红”“蓝”))
-
Gunn_Jack ✭✭✭✭
-
蒂娜Rustvold ✭✭✭✭✭
我只是在研究类似的东西时发现了这个帖子,但我们确实使用了4种颜色。这个公式可以调整到更多的哈维球的颜色吗?或者,我们是否需要在helper列中分配一个数值权重并从那里开始工作?谢谢!
-
迈克Wilday ✭✭✭✭✭✭
是的,它将适用于在Smartsheet的符号列上找到的任何哈维颜色。完整名单如下:
我希望这对你有帮助!!
帮助文章参考资料欧宝体育app官方888
If you have a unique ID (like ticket number) that is in both sheets, you could use DataMesh if you have access to that or cross sheet formulas to pull in the data you want based on the matching unique ID.<\/p>
Otherwise, you'd have to use an Add-on. If you are interested in hearing about an add-on that can do this, send me a message at https:\/\/smartsheetguru.com\/contact<\/a><\/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":106845,"type":"question","name":"Count function","excerpt":"Hi I need help with a formula. I have a column that I want to count but only based on a values in a different column. I have tried the following formula but it's not working and I can't figure out what I am doing wrong. =countif([Test Script #]:[Test Script #], [Pass ?]:[Pass ?], <> \"N\/A\") The Test Script # column is the…","categoryID":322,"dateInserted":"2023-06-23T17:50:06+00:00","dateUpdated":null,"dateLastComment":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":127983,"lastUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":46,"score":null,"hot":3375096049,"url":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106845\/count-function","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106845,"commentID":382093,"name":"Re: Count function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/382093#Comment_382093","dateInserted":"2023-06-23T19:30:43+00:00","insertUserID":127983,"insertUser":{"userID":127983,"name":"Carol-Anne Cerbone","url":"https:\/\/community.smartsheet.com\/profile\/Carol-Anne%20Cerbone","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!h1jY4Qxc4TY!prNYaQRZvvo!a6wiwaB2GsY","dateLastActive":"2023-06-23T19:29:42+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-23T19:30:46+00:00","dateAnswered":"2023-06-23T19:28:59+00:00","acceptedAnswers":[{"commentID":382092,"body":"