总体平均百分比

你好,

我已经看了很多表格,还没有找到一个解决方案,为什么我的公式是行不通的。我正在尝试确定子行的总项目完成百分比的公式。

对于项目启动行,我有公式=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

评论

  • TKeller
    TKeller ✭✭✭✭✭✭

    你好,玛西娅,

    您是否尝试使用“%完成系统”列?只要你的项目标题是最上面的父标题,其他的都是下面的子标题,它就会自动计算总体完成百分比。(你确实需要打开持续时间和前任,但这有助于管理整个项目)。

    希望这能有所帮助

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    我将建议创建一个隐藏的helper列(在本例中我们将其称为“helper”)。

    在辅助列的每个父行中使用

    =圆(如果条件统计(孩子([完成吗?]@row), 1) / COUNT(CHILDREN([完整?]@row))> 0, COUNTIF(CHILDREN([完整?]]@row), 1) / COUNT(CHILDREN([完整?]@row)), 0) * 100,0)

    这将给你号码。在[Complete?]]column, you can enter

    (电子邮件保护)+“%”

    这将把你的数字与百分号结合起来。

    对于整个行,您将进入Helper列

    =圆(AVG(儿童()),0)

    thinkspi.com

  • 上面说你让我输入的第一个公式是#无法解析。我想知道平均公式不能工作的原因是因为子行中有一个公式。

  • 嗨TKeller,

    我不熟悉%完成系统列。最父行是“任务名称”第11行。

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

    你好,玛西娅,

    %完成和其他项目表列是伟大的。

    请查看所附链接/截图了解更多信息。

    项目表列:开始日期,结束日期,持续时间,完成百分比和前任

    我希望这对你有所帮助!

    周末愉快!

    最好的

    她名叫Stara

    工作流顾问@完成咨询

    Smartsheet专家顾问和合作伙伴

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

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

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

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    =(条件统计(儿童([完成吗?]@row), 1) / COUNT(CHILDREN([完整?]@row)))* 100

    试试上面的方法。我(暂时)去掉了四舍五入之类的额外部分。这应该会给你一个数字,可能还有几个小数点。如果这可行,那么你可以将它包装在ROUND函数中,就像这样。

    =圆(条件统计(儿童([完成吗?]@row), 1) / COUNT(CHILDREN([完整?]@row)))* 1,0)

    thinkspi.com

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

    看看这里的项目表和%完成关于如何使用Smartsheet中的自动功能。

    https://app.smartsheet.com/b/publish?EQBCT=63979e622cc14da2a4b938194d31..。

    这样能行吗?

    最好的

    她名叫

    Smartsheet专家顾问和合作伙伴

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

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

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

  • 玛西娅Drabek
    编辑10/31/18

    这似乎对我的项目不起作用,我想是因为我想求平均的列是复选框列。

    https://app.smartsheet.com/b/publish?EQBCT=3fb3cc4472c441378a412fc0d57ff82e&_ga=2.214611219.585918219.1540809993-150492610.1536769947

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    好的。创建一个文本/数字帮助列(称为“Helper”)。在这一列,把这个公式写在所有绿色的行里。

    =圆(如果条件统计(孩子((电子邮件保护)), 1) / count (children ((电子邮件保护)) > 0, countif(儿童((电子邮件保护)), 1) / count (children ((电子邮件保护)), 0) * 100,0)

    然后在这一列的蓝色行,放这个。

    =圆(AVG(儿童()),0)

    在“完成”列中,将其放在绿色行和蓝色行中。

    (电子邮件保护)+“%”

    然后,您可以隐藏您的助手列,以保持工作表看起来干净。

    thinkspi.com

  • 帮了大忙,谢谢!

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

    是的,这将不起作用,因为它必须是一个数字/文本列。如果必须使用复选框,则必须按照Paul建议的方式执行。

    最好的

    她名叫

    Smartsheet专家顾问和合作伙伴

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

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

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

  • Ok. Create a text\/number Helper column (called \"Helper\"). In that column put this formula in all of your green rows.<\/p>\n

     <\/p>\n

    =ROUND(IF(COUNTIF(CHILDREN(Complete@row), 1) \/ COUNT(CHILDREN(Complete@row)) > 0, COUNTIF(CHILDREN(Complete@row), 1) \/ COUNT(CHILDREN(Complete@row)), 0) * 100, 0)<\/p>\n

     <\/p>\n

    Then in the Blue row of that column, put this.<\/p>\n

     <\/p>\n

    =ROUND(AVG(CHILDREN()), 0)<\/p>\n

     <\/p>\n

    In your Complete column, put this in the green rows and the blue row.<\/p>\n

     <\/p>\n

    =Helper@row + \"%\"<\/p>\n

     <\/p>\n

    You can then hide your helper column to keep your sheet looking clean.<\/p>","bodyRaw":"

    Ok. Create a text\/number Helper column (called \"Helper\"). In that column put this formula in all of your green rows.<\/p>\n\n

     <\/p>\n\n

    =ROUND(IF(COUNTIF(CHILDREN(Complete@row), 1) \/ COUNT(CHILDREN(Complete@row)) > 0, COUNTIF(CHILDREN(Complete@row), 1) \/ COUNT(CHILDREN(Complete@row)), 0) * 100, 0)<\/p>\n\n

     <\/p>\n\n

    Then in the Blue row of that column, put this.<\/p>\n\n

     <\/p>\n\n

    =ROUND(AVG(CHILDREN()), 0)<\/p>\n\n

     <\/p>\n\n

    In your Complete column, put this in the green rows and the blue row.<\/p>\n\n

     <\/p>\n\n

    =Helper@row + \"%\"<\/p>\n\n

     <\/p>\n\n

    You can then hide your helper column to keep your sheet looking clean.<\/p>","format":"html","dateInserted":"2018-10-31T08:15:39+00:00","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":"2022-08-05T20:10:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/112411#Comment_112411","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/112411#Comment_112411

    保罗,我知道这是一个旧的话题,但它很有帮助。通过剖析这个问题,并将我所学到的应用到我的公式中,我能够找出很多其他的问题。谢谢你!

帮助文章资源欧宝体育app官方888

I could solve the issue. It was a simple spelling mistake or difference between the two sheets.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"reasonUpdated":"","dateUpdated":"2022-08-06 20:28:39","updateUser":{"userID":151036,"name":"Jörg Schmidt1","url":"https:\/\/community.smartsheet.com\/profile\/J%C3%B6rg%20Schmidt1","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!hJjVoY907bY!!P7w61eHMiE7","dateLastActive":"2022-08-06T20:26:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":219,"urlcode":"Sheets","name":"Sheets"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"}]},{"discussionID":93987,"type":"question","name":"how can i copy a value from one cell to another in the same sheet","excerpt":"I have the above project plan setup The FOCUS AREA is a dropdown value. When i add children to a parent, i want the children to inherit the parents FOCUS AREA value. i created a helper column to identify children and that childs parent value for focus area. How can i automate the copy over AND still allow the user to pick…","categoryID":322,"dateInserted":"2022-08-05T17:50:56+00:00","dateUpdated":null,"dateLastComment":"2022-08-05T20:20:49+00:00","insertUserID":120623,"insertUser":{"userID":120623,"name":"Umesh Shah","url":"https:\/\/community.smartsheet.com\/profile\/Umesh%20Shah","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!B_DAgW7inuQ!GutBH5L31vo!a4sw_iKY2Cf","dateLastActive":"2022-08-05T20:19:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":120623,"lastUser":{"userID":120623,"name":"Umesh Shah","url":"https:\/\/community.smartsheet.com\/profile\/Umesh%20Shah","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!B_DAgW7inuQ!GutBH5L31vo!a4sw_iKY2Cf","dateLastActive":"2022-08-05T20:19:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":14,"score":null,"hot":3319453905,"url":"https:\/\/community.smartsheet.com\/discussion\/93987\/how-can-i-copy-a-value-from-one-cell-to-another-in-the-same-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/93987\/how-can-i-copy-a-value-from-one-cell-to-another-in-the-same-sheet","format":"Rich","lastPost":{"discussionID":93987,"commentID":338859,"name":"Re: how can i copy a value from one cell to another in the same sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/338859#Comment_338859","dateInserted":"2022-08-05T20:20:49+00:00","insertUserID":120623,"insertUser":{"userID":120623,"name":"Umesh Shah","url":"https:\/\/community.smartsheet.com\/profile\/Umesh%20Shah","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!B_DAgW7inuQ!GutBH5L31vo!a4sw_iKY2Cf","dateLastActive":"2022-08-05T20:19: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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/353Y6W0RK5SL\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-08-05T20:21:04+00:00","dateAnswered":"2022-08-05T19:49:10+00:00","acceptedAnswers":[{"commentID":338851,"body":"

You would use<\/p>

=PARENT([Focus Area]@row)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"reasonUpdated":"","dateUpdated":"2022-08-05 20:21:04","updateUser":{"userID":120623,"name":"Umesh Shah","url":"https:\/\/community.smartsheet.com\/profile\/Umesh%20Shah","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!B_DAgW7inuQ!GutBH5L31vo!a4sw_iKY2Cf","dateLastActive":"2022-08-05T20:19:13+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":93984,"type":"question","name":"Multiselect Dropdown Parsing","excerpt":"Hello all, I have 3 columns in a sheet. 1 is a multi-select dropdown (\"MSD\"), 1 is a single contact (\"SC\"), and 1 is a multi-select contact (\"MSC\"). In the SC, I want to take the first dropdown value from the MSD and use it to index match a value from another sheet. In the MSC, then I want to take the remaining values from…","categoryID":322,"dateInserted":"2022-08-05T17:28:01+00:00","dateUpdated":null,"dateLastComment":"2022-08-05T19:48:02+00:00","insertUserID":151077,"insertUser":{"userID":151077,"name":"Allen M Vance","url":"https:\/\/community.smartsheet.com\/profile\/Allen%20M%20Vance","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wvjQf0zrtpU!HVPSXzyfi_0!-X5Gyhg_Kh5","dateLastActive":"2022-08-05T22:14:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"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":"2022-08-05T20:10:09+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":10,"score":null,"hot":3319449963,"url":"https:\/\/community.smartsheet.com\/discussion\/93984\/multiselect-dropdown-parsing","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/93984\/multiselect-dropdown-parsing","format":"Rich","lastPost":{"discussionID":93984,"commentID":338850,"name":"Re: Multiselect Dropdown Parsing","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/338850#Comment_338850","dateInserted":"2022-08-05T19:48:02+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":"2022-08-05T20:10:09+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":"2022-08-05T22:14:17+00:00","dateAnswered":"2022-08-05T19:48:02+00:00","acceptedAnswers":[{"commentID":338850,"body":"

Technically yes. You can pull these values in based on the multi-select dropdown, but...<\/p>


<\/p>

We cannot currently use formulas to populate multiple USABLE contacts in a single cell even if the column is set to allow multiple. It will simply generate a text string and won't be usable in automations for notifications, update requests, etc..<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"reasonUpdated":"","dateUpdated":"2022-08-05 22:14:17","updateUser":{"userID":151077,"name":"Allen M Vance","url":"https:\/\/community.smartsheet.com\/profile\/Allen%20M%20Vance","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!wvjQf0zrtpU!HVPSXzyfi_0!-X5Gyhg_Kh5","dateLastActive":"2022-08-05T22:14:05+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]}],"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">