If函数-嵌套-负百分比-符号
我有一个让我抓狂的配方。我试图让符号根据计划列的百分比改变颜色(见截图)。如果%为正，我希望On Track列显示绿色。如果它是负的，但大于-25%，我想显示黄色，所有其他显示红色。问题是我得到了一个#除以零错误。(仅供参考，计划的百分比是一个公式，它=[实际2023]@row -[23预算MTD]@row)
最佳答案
-
卡森Penticuff ✭✭这种情况适用于列式公式。我会尝试将上面的Ray公式粘贴到列的第一个单元格中，然后转换为列公式。
答案
-
卡森Penticuff ✭✭
计划百分比是一个公式，它是=[实际2023]@row -[23预算MTD]@row)
你能再检查一下那个公式吗?数学似乎不符合这一点，也不会得出百分比。
-
雷林德斯特伦 ✭✭✭✭✭✭你好,
我想用下面的东西。
如果([%]计划@row > 0,“绿色”,如果(((%计划)@row > -。25、“黄色”、“红色”))
如果大于0，则为绿色
如果大于-。25, then yellow (anything greater than 0 would have already been caught and broke the if sequence)
“其他的”，瑞德
-
艾丽卡梅纳德 ✭✭✭
@Carson Penticuff你是对的，它实际上是=[Var to Budget]@row / [23 Budget MTD]@row
-
艾丽卡梅纳德 ✭✭✭
-
卡森Penticuff ✭✭
这个公式是列式吗?你所展示的不应该能够产生除零的错误，因为公式本身没有除法。
上面的帖子@Ray林德斯特伦显示了你应该使用的公式，但我仍然没有解释你发布的公式是如何产生错误的。
-
艾丽卡梅纳德 ✭✭✭
@Carson Penticuff这就是为什么它让我抓狂，因为我同意它不应该产生那个错误。它不设为列式公式;我不知道还能做什么;我不想在每个月数据更新时手动将这些更改为适当的颜色，但我可能不得不这样做。
-
卡森Penticuff ✭✭
这种情况适用于列式公式。我会尝试将上面的Ray公式粘贴到列的第一个单元格中，然后转换为列公式。
-
艾丽卡梅纳德 ✭✭✭
@Carson Penticuff使它成为一个列式公式。谢谢你的建议。
-
卡森Penticuff ✭✭
我猜可能在一个或多个单元格中出现了打字错误。将其设置为列公式可确保它们都是相同的，并节省逐个单元格查找难以检测的错误的时间和精力。我很高兴修好了!
帮助文章参考资料欧宝体育app官方888
类别
Try: =SUM([Amount of fees Waived]:[Amount of fees Waived]) + SUM([Amount Written off]:[Amount Written off]) + SUM([Attorney fees]:[Attorney fees])<\/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":106336,"type":"question","name":"True count of Children(), not just non blanks","excerpt":"Doing project roll up and trying to use the formula below to roll up a complete status. =IF(COUNTIF(CHILDREN(), \"Complete\") = COUNT(CHILDREN()), \"Complete\", \"Not\") However, blank cells do not contribute to the COUNT(CHILDREN()), thus if there are three children and someone deletes all values in 3 cells or any non…","categoryID":322,"dateInserted":"2023-06-12T20:08:17+00:00","dateUpdated":"2023-06-12T20:09:57+00:00","dateLastComment":"2023-06-12T22:39:12+00:00","insertUserID":161938,"insertUser":{"userID":161938,"name":"vanZylin","url":"https:\/\/community.smartsheet.com\/profile\/vanZylin","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/DWO5546FHG2S\/n63YWEB75TVCL.jpg","dateLastActive":"2023-06-12T21:09:22+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":161938,"lastUserID":45516,"lastUser":{"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-06-13T00:08:31+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3373211849,"url":"https:\/\/community.smartsheet.com\/discussion\/106336\/true-count-of-children-not-just-non-blanks","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106336\/true-count-of-children-not-just-non-blanks","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106336,"commentID":380221,"name":"Re: True count of Children(), not just non blanks","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380221#Comment_380221","dateInserted":"2023-06-12T22:39:12+00:00","insertUserID":45516,"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-06-13T00:08:31+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-12T20:39:58+00:00","dateAnswered":"2023-06-12T20:12:44+00:00","acceptedAnswers":[{"commentID":380185,"body":"
Instead of comparing to the count of children, you would compare to the count of children that are blank or not blank.<\/p>
<\/p>
Change<\/p>
COUNT(CHILDREN())<\/p>
to<\/p>
COUNTIFS(CHILDREN(), OR(@cell <> \"\", @cell = \"\"))<\/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"}]}],"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":9975,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
