RYG球自动化

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

我是一个智能表新手，我最终试图建立一个公式，根据以下内容自动执行任务进度:

如果% Complete = 100，返回蓝色

如果还没有开始，返回Clear

如果超过了预定的完成日期，则返回Red

如果% complete >=(今天日期-开始日期)/任务持续时间，返回Green

否则，返回黄色。

在研究了if语句之后，我想至少从第一个简单的if语句开始，如果任务100%完成，返回蓝球。下面是我使用的公式，但没有返回在单元格。

=IF([% complete]1 = 100， "Blue")

附上截图

RYG任务指示器自动化。png

标签:
· 在Facebook上分享 在Twitter上分享

评论

  • Brian W
    Brian W ✭✭
    编辑01/25/19

    你很接近了。在公式中，% complete是一个0-1的范围，所以它应该是:

    =IF([% complete]1 = 1， "Blue")

    如果你想要超过50%，它会是这样的:

    =IF([% complete]1 > .5， "蓝色")

    如果你需要我帮忙做剩下的公式，请告诉我。

    · 在Facebook上分享 在Twitter上分享
  • RawRobb
    RawRobb ✭

    谢谢你! !我痴迷于让这个公式起作用，我想如果我把各个部分分开，那么我就可以在测试完每个部分后把它们结合起来。这是最简单的一块。好的。好了，我要开始下一块了。希望你能在线回答我的下一个问题!非常感谢您的快速回复。

    · 在Facebook上分享 在Twitter上分享
  • RawRobb
    RawRobb ✭
    编辑01/28/19

    我弄明白了，这里是分解，所以我的RYG状态指示器是基于下面的自动的，你只需要输入你的手动完成百分比。不错，因为我从未使用过smartsheet，我是基本的excel用户，从未使用过MS项目：）

    • 对于非零持续时间的任务
      • 如果% Complete = 100，返回蓝色
        • =IF([% complete]1 = 1， "Blue")
      • 如果尚未计划启动，并且% complete为空，则返回Clear
        • =如果今天((()< ISBLANK(开始日期)7日([%完成]7))" ")
      • 如果已超过预定的结束日期，且“完成%”小于100%，则返回红色
        • =如果今天((()>(结束日期)2,[%]完成2 < 1),“红色”)
    • 如果%完成<按比例预期完成(如果完成百分比小于任务计划开始后的天数，除以任务持续时间，则为黄色;否则，它将是绿色的。
        • =IF([% Complete]3 < (TODAY() - [Start date]3) / Duration3， "Yellow"， "Green")
    • 所有这些结合起来
        • =IF([% Complete]10 = 1， "蓝色"，IF(AND(TODAY() <[开始日期]10,ISBLANK([% Complete]10))， ""， IF(TODAY() >[结束日期]10，"红色"，IF([% Complete]10 < (TODAY() -[开始日期]10)/ Duration10， "黄色"，"绿色"))))
    · 在Facebook上分享 在Twitter上分享
  • Brian W
    Brian W ✭✭

    很好地完成。这些嵌套的IF语句可能会很棘手。

    · 在Facebook上分享 在Twitter上分享

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
Hi @Clare123<\/a> <\/p>

I hope you're well and safe!<\/p>

Yes, absolutely.<\/p>

Here's the structure.<\/p>

=INDEX({ColumnWithTheValueYouWantToShow}, MATCH(CellThatHaveTheValueToMatch@row,{ColumnWithTheValueToMatchAgainsTheCell}, 0))<\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/p>

Best,<\/p>

Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p>

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":106314,"type":"question","name":"Flexible Duration value when calculating scheduling dates","excerpt":"I have a Smartsheet which is a project plan with the Dependencies and Duration columns is being used in scheduling calculations to automatically deriving the start\/end dates for each task. However, I need the Duration to be dynamic e.g. linking cell to a cell in another sheet or to contain a formulae.. However, I cannot…","categoryID":322,"dateInserted":"2023-06-12T12:39:46+00:00","dateUpdated":null,"dateLastComment":"2023-06-13T11:22:45+00:00","insertUserID":162290,"insertUser":{"userID":162290,"name":"Mark Duddy","title":"Program Manager","url":"https:\/\/community.smartsheet.com\/profile\/Mark%20Duddy","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-13T11:41:04+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-13T12:20:15+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":51,"score":null,"hot":3373230151,"url":"https:\/\/community.smartsheet.com\/discussion\/106314\/flexible-duration-value-when-calculating-scheduling-dates","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106314\/flexible-duration-value-when-calculating-scheduling-dates","format":"Rich","lastPost":{"discussionID":106314,"commentID":380264,"name":"Re: Flexible Duration value when calculating scheduling dates","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380264#Comment_380264","dateInserted":"2023-06-13T11:22:45+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-13T12:20:15+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-13T11:24:19+00:00","dateAnswered":"2023-06-13T11:22:45+00:00","acceptedAnswers":[{"commentID":380264,"body":"

Hi @Mark Duddy<\/a> <\/p>

If you're using dependencies in a Project Sheet, then the Duration column is not able to be updated via formulas <\/a>or cell links. This is because it's used in combination with the Start and Finish dates in your sheet to automatically generate data (e.g. if you add the Start date and a Duration, the Finish date populates. If you add the Start date and Finish date, the Duration automatically populates).<\/p>

You could set up your sheet to use regular columns and use your own formulas to generate automatic dates based on a text\/number \"duration\" column, but then these details would not be associated with Critical paths or other Project features in a Gantt chart.<\/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":[]},{"discussionID":106342,"type":"question","name":"I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula","excerpt":"I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula. Example of 2 IF formulas: =IF([Member 2 Registration Type]@row = \"Golf & Dinner - General Public\", \"135\") and =IF([Member 2 Registration Type]@row = \"Dinner Only\", \"60\") The IF formulas are working as they should and are…","categoryID":322,"dateInserted":"2023-06-12T21:19:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-13T02:23:40+00:00","insertUserID":162311,"insertUser":{"userID":162311,"name":"Jhinkle","url":"https:\/\/community.smartsheet.com\/profile\/Jhinkle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!209A_9K4WHo!nZ9On1SJEvo!KaPQ5aRebqf","dateLastActive":"2023-06-13T02:23:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162311,"lastUser":{"userID":162311,"name":"Jhinkle","url":"https:\/\/community.smartsheet.com\/profile\/Jhinkle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!209A_9K4WHo!nZ9On1SJEvo!KaPQ5aRebqf","dateLastActive":"2023-06-13T02:23:49+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":21,"score":null,"hot":3373228985,"url":"https:\/\/community.smartsheet.com\/discussion\/106342\/i-am-trying-to-use-sum-to-add-the-values-from-multiple-cells-in-that-were-populated-by-an-if-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106342\/i-am-trying-to-use-sum-to-add-the-values-from-multiple-cells-in-that-were-populated-by-an-if-formula","format":"Rich","lastPost":{"discussionID":106342,"commentID":380235,"name":"Re: I am trying to use SUM to add the values from multiple cells in that were populated by an IF formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/380235#Comment_380235","dateInserted":"2023-06-13T02:23:40+00:00","insertUserID":162311,"insertUser":{"userID":162311,"name":"Jhinkle","url":"https:\/\/community.smartsheet.com\/profile\/Jhinkle","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!209A_9K4WHo!nZ9On1SJEvo!KaPQ5aRebqf","dateLastActive":"2023-06-13T02:23:49+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-13T02:23:47+00:00","dateAnswered":"2023-06-13T02:23:40+00:00","acceptedAnswers":[{"commentID":380235,"body":"

Thanks! That worked!<\/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":9977,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

公式和函数趋势

  • 查找工作表B中的多个列值以匹配工作表A中的值以返回工作表B中的值

    你好，我试图使一个公式，计数从表a对表B中的多个列的值，如果只找到1个总匹配，然后查找值从表a对表B，并根据结果，从状态列拉值。如果有太多相同的结果或没有找到结果返回一个文本…

  • 计算调度日期时的弹性持续时间值

    我有一个智能表，这是一个项目计划的依赖关系和持续时间列正在调度计算中使用，以自动派生每个任务的开始/结束日期。然而，我需要持续时间是动态的，例如链接单元格到另一个工作表中的单元格或包含公式。然而，我不能……

  • 我试图使用SUM来添加由IF公式填充的多个单元格中的值

    我试图使用SUM来添加由IF公式填充的多个单元格中的值。2个IF公式的例子:=IF([成员2注册类型]@row =“高尔夫和晚餐-一般公众”，“135”)和=IF([成员2注册类型]@row =“仅限晚餐”，“60”)IF公式正在工作，因为他们应该是……