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

Options
✭✭
edited 02/20/23

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?

• ✭✭✭✭✭✭
edited 02/20/23 Answer ✓
Options

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

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

• ✭✭✭✭✭✭
edited 02/20/23
Options

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

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

• ✭✭
Options

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

• ✭✭✭✭✭✭
edited 02/20/23 Answer ✓
Options

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

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

• ✭✭
edited 02/21/23
Options

@Jeff Reisman Thank you! Changing it to DESCENDANTS work. Such an easy fix.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!