Why is my formula giving me incorrect average when trying to average the children rows?
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

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.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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!
Answers

Perhaps you could share the formula you're using and the data structure you're using it on?Thanks, that's easier to troubleshoot, LOL!
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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!

@Jeff Reisman Yeah I accidently posted the discussion without inputting anything. I have edited the original one.

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.
Regards,
Jeff Reisman, IT Business Analyst & Project Coordinator, Mitsubishi Electric Trane US
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!

@Jeff Reisman Thank you! Changing it to DESCENDANTS work. Such an easy fix.
Help Article Resources
Categories
Check out the Formula Handbook template!