整体平均竞争率

玛西娅Drabek
玛西娅Drabek ✭
编辑12/09/19 在公式和函数

你好,

我已经环顾四周的多种形式，并没有能够找到一个解决方案，为什么我的公式不工作。我试图确定的公式为总项目%完成的孩子行。

对于Project Initiation行，我有公式=ROUND(IF(COUNTIF(CHILDREN()， 1) / COUNT(CHILDREN()) > 0, COUNTIF(CHILDREN()， 1) / COUNT(CHILDREN())， 0) * 100,0) + "%"

为完成?11cell I tried to use the formula =AVG(CHILDREN()) and I get a #DIVIDE BY ZERO error.

我如何得到完成?11cell to show me the total percentage for the entire project? Please note this sheet is for project templates.

% Completed.PNG

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

评论

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

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

交叉表引用的公式组合

智能表功能列表

类别

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

请查看公式手册模板!
[Date of Site Survey (Product)]@row, \"yes\", \" \")) Intended logic\/result - If date cell is blank, then \"blank\" (this worked when I tried without the 2nd argument added) If date is in…","categoryID":322,"dateInserted":"2023-06-09T14:00:25+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T14:29:50+00:00","insertUserID":162220,"insertUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T15:40:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":162220,"lastUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T15:40:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":22,"score":null,"hot":3372641415,"url":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106265\/if-date-is-blank-past-formula","format":"Rich","lastPost":{"discussionID":106265,"commentID":379837,"name":"Re: If Date is blank + past formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379837#Comment_379837","dateInserted":"2023-06-09T14:29:50+00:00","insertUserID":162220,"insertUser":{"userID":162220,"name":"dhawkins","title":"Product Manager","url":"https:\/\/community.smartsheet.com\/profile\/dhawkins","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T15:40:13+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-09T14:28:35+00:00","dateAnswered":"2023-06-09T14:12:11+00:00","acceptedAnswers":[{"commentID":379830,"body":"

Try this:<\/p>

=IF([Date of Site Survey (Product)]@row <> \"//www.santa-greenland.com/community/discussion/comment/\", IF(TODAY() > [Date of Site Survey (Product)]@row, \"yes\", \"no\"))<\/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":106249,"type":"question","name":"Modifying a date based on 2 variables","excerpt":"Hello Everyone! I would like to create a formula that says If the value in the \"Variable\" column is \"HIGH\" AND the date in \"Column 1\" has a year less than today's year, return that date with TODAY\"S year. Otherwise return that date unchanged. In other words, dates in the previous years are brought to this year, dates in…","categoryID":322,"dateInserted":"2023-06-08T23:28:09+00:00","dateUpdated":null,"dateLastComment":"2023-06-09T15:45:32+00:00","insertUserID":127672,"insertUser":{"userID":127672,"name":"Carroll Wall","url":"https:\/\/community.smartsheet.com\/profile\/Carroll%20Wall","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-06-09T15:44:11+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-06-09T16:56:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":9,"countViews":45,"score":null,"hot":3372597821,"url":"https:\/\/community.smartsheet.com\/discussion\/106249\/modifying-a-date-based-on-2-variables","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106249\/modifying-a-date-based-on-2-variables","format":"Rich","tagIDs":[216,234,249,254],"lastPost":{"discussionID":106249,"commentID":379864,"name":"Re: Modifying a date based on 2 variables","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379864#Comment_379864","dateInserted":"2023-06-09T15:45:32+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-06-09T16:56:38+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\/3EAGX5UGDNCQ\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-06-09T15:44:08+00:00","dateAnswered":"2023-06-09T15:30:15+00:00","acceptedAnswers":[{"commentID":379858,"body":"

Sure, here is how it breaks down:<\/p>

=IF([Variable]@row <> \"High\", \"//www.santa-greenland.com/community/discussion/comment/\"\n<\/pre>
<> is a way of saying \"not equal to\". So, in this case, we are saying \"Do the following thing if [Variable] is not equal to \"High\". The double quotes, \"//www.santa-greenland.com/community/discussion/comment/\", is another way of saying \"blank\". So if our formula sees that [Variable] contains anything other than \"High\", it is going to set [Goal Column] as blank.<\/p>
IF(YEAR(TODAY()) > YEAR([First Column]@row), DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)), [First Column]@row))\n<\/pre>
This section of the formula applies to the \"else\" part of the original IF statement. The first part looked for [Variable] not equal to \"High\", so this part will apply to [Variable] that does equal \"High\". First, we are asking whether today's year, YEAR(TODAY(), is greater than the year of [First Column], YEAR([First Column]). If it is, we are going to build a date using the present year, combined with the month and day from [First Column], DATE(YEAR(TODAY()), MONTH([First Column]@row), DAY([First Column]@row)).<\/p>
[First Column]@row\n<\/pre>
Lastly, if the current year is not greater than [First Column], i.e., they are the same, then we are simply going to set [Goal Column] to the same value as [First Column].<\/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":216,"urlcode":"Non-profit","name":"Non-profit"},{"tagID":234,"urlcode":"Health-care","name":"Healthcare"},{"tagID":249,"urlcode":"Education","name":"Education"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":106240,"type":"question","name":"How to make a manual Row ID based on conditions?","excerpt":"I'm not even sure if this is possible but I invite all the formula wizards to take a crack at it! I have this intake sheet with 10 rows, all that have a corresponding auto-generated Row ID from the system column. Row 1 is 23-001, row 2 is 23-002, row 3 is 23-003, etc. When a project gets approved, I want another column,…","categoryID":322,"dateInserted":"2023-06-08T20:55:41+00:00","dateUpdated":"2023-06-09T07:54:22+00:00","dateLastComment":"2023-06-09T16:11:17+00:00","insertUserID":155918,"insertUser":{"userID":155918,"name":"Gabriel Barrera","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Gabriel%20Barrera","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!m4MvHwJdY3o!cpDsp75M-3o!0p-TgKs_b-h","dateLastActive":"2023-06-09T16:09:18+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"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-09T16:55:11+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":22,"score":null,"hot":3372587218,"url":"https:\/\/community.smartsheet.com\/discussion\/106240\/how-to-make-a-manual-row-id-based-on-conditions","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/106240\/how-to-make-a-manual-row-id-based-on-conditions","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":106240,"commentID":379878,"name":"Re: How to make a manual Row ID based on conditions?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/379878#Comment_379878","dateInserted":"2023-06-09T16:11:17+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-09T16:55:11+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-09T16:09:16+00:00","dateAnswered":"2023-06-09T13:52:50+00:00","acceptedAnswers":[{"commentID":379821,"body":"
Hi @Gabriel Barrera<\/a> <\/p>
Here's an example formula that may be able to help you:<\/p>
=IF(Status@row <> \"Approved\", \"//www.santa-greenland.com/community/discussion/comment/\", INDEX([Row ID]:[Row ID], COUNTIF(Status$1:Status@row, \"Approved\")))<\/p>
\n    
\n        \n            \"Screenshot<\/img><\/a>\n    <\/div>\n<\/div>\n

<\/p>
This first says, if the Status is not<\/strong> approved, then return a blank cell. <\/p>
=IF(Status@row <> \"Approved\", \"//www.santa-greenland.com/community/discussion/comment/\",<\/p>
Otherwise, use the INDEX function to look into the Row ID column and bring back the row on the list that's the same number of \"Approved\" rows there are, specific to this current row. <\/p>
Notice that the COUNTIF function references the top row cell: Status$1 and then the range goes down to the current row cell: Status@row<\/p>
Because of this, you won't be able to turn it into a column formula. However you can drag-fill this down the column and then all new rows will auto-populate with the formula as long as new rows are added immediately under current rows. See: Use or Override Automatic Formatting and Formula Autofill<\/a><\/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"}]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
             

               

                
公式和函数趋势

               

               
    
                
  • 如果Date是空白+过去公式
    我不明白为什么这将返回一个“是”当日期单元格是空白。=IF([现场调查(产品)日期]@row = " "ob欧宝娱乐app手机下载， " "， IF(今天()>[现场调查(产品)日期]@row， "是"，" ")预期的逻辑/结果-如果日期单元格为空白，则"空白"(当我尝试不添加第二个参数时有效)如果日期在…
    • 
                
  • 基于2个变量修改日期
    大家好!我想创建一个公式，如果“变量”列中的值为“HIGH”，并且“第1列”中的日期比今天的年份短，则返回该日期为“今天”。否则，返回日期不变。换句话说，前几年的日期被带到今年，在…
    • 
                
  • 如何根据条件手工创建行号?
    我甚至不确定这是否可能，但我邀请所有的公式巫师在它的裂缝!我有这个进气表与10行，所有有相应的自动生成的行ID从系统列。第一行是23-001，第二行是23-002，第三行是23-003，以此类推。当一个项目被批准时，我想要另一个专栏……
    •