如果单元格为空或空白,

SBash
SBash ✭✭✭✭
编辑12/09/19 公式和函数

你好,

我目前制定了以下公式,

=IF([天数(差异预测-基线)]1 <= 5,"绿色",IF(AND([天数(差异预测-基线)]1 > 5,[天数(差异预测-基线)]1 <= 10),"琥珀色","红色"))

这是有效地工作,但问题发生时,单元格是空的,它显示我返回“绿色”。正如我的公式所说,“如果值<=5,则放绿色”。所以它取一个空单元格<并显示绿色

如何使用ISBlank公式将空单元格或“-”

或者怎样才能克服这样的情况

非常感谢

评论

  • 尼克拉森
    尼克拉森 ✭✭✭✭✭✭

    只需将其添加到公式的前面作为初始检查:

    =IF(ISBLANK([日(差值预测-基线)])@row,“”,IF([日(差值预测-基线)]@row<= 5,“绿色”,IF(AND([天(差值预测-基线)]@row> 5,[天(差值预测-基线)]@row<= 10),“琥珀色”,“红色”))

  • SBash
    SBash ✭✭✭✭

    嗨,尼克,

    我已经尝试了你上面提到的说明,但不幸的是,结果是“错误的观点”

    作为参考,我附上了截图

    非常感谢

    Smartsheet.PNG

  • 她名叫Stara
    她名叫Stara ✭✭✭✭✭✭

    你好,

    你能分享一下公式或表格吗?(在分享之前删除/替换任何机密/敏感信息)这样更容易提供帮助。(分享,(电子邮件保护)

    祝你一周愉快!

    最好的

    她名叫Stara

    工作流程顾问@完成咨询

    智能表专家顾问兼合伙人

    她名叫Stara|工作流程顾问/ CEO @工作大胆

    W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35

    请随时与我联系,了解有关Smartsheet,集成,一般工作流建议或其他方面的帮助。

  • SBash
    SBash ✭✭✭✭
    编辑04/09/19

    嗨,她名叫

    目前我还没有固定的数据,我已经采取了一些虚拟数据进行测试,我已经做了一个模板。背后的逻辑是计算与基线完成日期和预测完成日期比较的天数。

    完成日期计划完工日期

    预测实际完成日期=实际完工日期

    日(差值预测-基线)=两个日期之间的差

    项目交付状态条件逻辑

    如果预测实际完成天数大于或等于基线完成日期,但小于5天,则将RAG设置为绿色

    如果预测实际完成天数大于基线完成日期5天,但小于或等于10天,则将RAG设置为黄色

    如果预测实际完成天数距离基线完成日期大于10天,则将RAG设置为红色

    公式= IF([天数(差异预测-基线)]1 <= 5,"绿色",IF(AND([天数(差异预测-基线)]1 > 5,[天数(差异预测-基线)]1 <= 10),"黄色","红色"))

    它工作得很好,但问题出现了,RAG显示绿色,即使日(差值预测-基线)”是空白。如公式<=5所示,“绿色”

    我需要支持,如何能把字段空或“-”,如果差异行是空白

    非常感谢

  • 尼克拉森
    尼克拉森 ✭✭✭✭✭✭

    我上次可能漏了什么。我已经测试了这个,它是为我工作:

    =IF(ISBLANK([日(差值预测-基线)])@row),“”,IF([日(差值预测-基线)]@row<= 5,“绿色”,IF(AND([天(差值预测-基线)]@row> 5,[天(差值预测-基线)]@row<= 10), "Amber", "Red")))

  • SBash
    SBash ✭✭✭✭

    嗨,尼克,

    非常感谢,你的公式有效

    另一方面,我也并行地建立了这个公式它显示了同样的效果。

    =IF([天数(差异预测-基线)]2 = "","",IF([天数(差异预测-基线)]2 <= 5,"绿色",IF(AND([天数(差异预测-基线)]2 > 5,[天数(差异预测-基线)]2 <= 10),"黄色","红色"))

    你认为两者会有什么不同吗?意味着任何缺陷

    非常感谢

    致以最亲切的问候

    Saqib

  • 尼克拉森
    尼克拉森 ✭✭✭✭✭✭

    你的公式应该没问题。虽然我会用@row.它只是让它更有效率。

    https://help.smartsheet.com/articles/2476491-create-efficient-formulas-with-at-cell

  • SBash
    SBash ✭✭✭✭

    嗨,尼克,

    非常感谢

    最好的问候,

    saqib

  • 她名叫Stara
    她名叫Stara ✭✭✭✭✭✭

    很乐意帮忙!

    我看到尼克已经接电话了!

    如果还有什么需要我帮忙的,请告诉我!

    最好的

    她名叫

    智能表专家顾问兼合伙人

    她名叫Stara|工作流程顾问/ CEO @工作大胆

    W:www.workbold.com| E:(电子邮件保护)| p: +46 (0) - 72 - 510 99 35

    请随时与我联系,了解有关Smartsheet,集成,一般工作流建议或其他方面的帮助。

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

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

请查看公式手册模板!
Hey @Marilen.Navarro103391<\/a> <\/p>

Please use the formula I provided. It was intentional for the character \"~\" to be included. <\/p>

=MID(Name@row, FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 5)) + 1, FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 6)) - 1 - FIND(\"~\", SUBSTITUTE(Name@row, \"-\", \"~\", 5)))<\/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":106552,"type":"question","name":"Problem w\/Index - Match formula","excerpt":"I using the following formula, =INDEX({Contract Focal Point}, MATCH([Contract No.]@row, {Contract No.}), 0) And it works for the first row when i input in the contract number into the column. On subsequent rows when i input the contract number it pulls back incorrect information and if I put in a fictious number it pulls…","categoryID":322,"dateInserted":"2023-06-16T15:13:32+00:00","dateUpdated":null,"dateLastComment":"2023-06-16T15:36:24+00:00","insertUserID":162467,"insertUser":{"userID":162467,"name":"Ed Smartsheet","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Ed%20Smartsheet","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJyYOjKt3k3tneIFbUPuoJzZbnd9quLS-Wdt5b7l=s96-c","dateLastActive":"2023-06-16T19:45:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-16T17:50:53+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":29,"score":null,"hot":3373859396,"url":"https:\/\/community.smartsheet.com\/discussion\/106552\/problem-w-index-match-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106552\/problem-w-index-match-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106552,"commentID":381031,"name":"Re: Problem w\/Index - Match formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/381031#Comment_381031","dateInserted":"2023-06-16T15:36:24+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-06-16T17:50:53+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"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-16T19:45:15+00:00","dateAnswered":"2023-06-16T15:36:24+00:00","acceptedAnswers":[{"commentID":381031,"body":"

Hey @Ed Smartsheet<\/a> <\/p>

In addition to the IFERROR, it looks like you have the , 0 in the INDEX portion of the formula instead of the MATCH. Try moving it in one:<\/p>

=INDEX({Contract Focal Point}, MATCH([Contract No.]@row, {Contract No.}, 0))<\/strong><\/p>

Cheers,<\/p>

Genevieve<\/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"}]},{"discussionID":106550,"type":"question","name":"Invalid Operation","excerpt":"Good morning, I am trying to sum the entries that are greater than 30 but less than 60 days old. =SUMIFS({011-AP Archive -# of Days to Complete}, >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":34,"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":[]}],"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":[]}">

公式和函数趋势