Why is my formula giving me incorrect average when trying to average the children rows?

Shadany ✭✭
edited 02/20/23 in Formulas and Functions

I have a formula that is averaging the percentage of the children row of the column Percent completed, to give me another percentage of the ones that are less than 100% and then I have another column that will average the percentage of the ones that are greater than 100%. But it is not giving me a correct percentage. The screen shot should average 75% in the Goal Not Met column but it's returning as 59%. Is there something I need to change in my formula?

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/20/23 Answer ✓


    The Child rows of the dark blue row, Item 99511, are only the rows with the PROD number in yellow:

    So the average of 55% and 63% = 59%.

    Try using DESCENDANTS instead of CHILDREN in your formula and see if that considers all the rows under 100%.

    Or, if you want all the rows to be direct children of 99511, then outdent the rows in the green boxes.


    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!