How can I average a percentage in a parent row?


I have a status column that uses Red, Yellow, Green and Gray to represent "Not Complete", "Partially Complete", "Complete" and "Not Applicable". I successfully added a progress column that uses a formula to translate those statuses into a percentage. However, since the parent row also has a status color, I can't get the formula to take an average on the parent row. Here is the formula I am using. I tried removing the "IFERROR" part of the formula (see below) and get an unparseable result. Any suggestions?

=IFERROR(AVG(CHILDREN(Progress@row)), IF(Status@row = "Green", "100%", IF(Status@row = "Yellow", "50%", IF(Status@row = "Red", "0%", IF(Status@row = "Gray", "0%")))))

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!