How can I average a percentage in a parent row?

lora.riggs

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%")))))

Paul Newcome

Try this:

=IFERROR(AVG(CHILDREN()), IF(Status@row = "Green", 1, IF(Status@row = "Yellow", .5, IF(Status@row = "Red", 0, IF(Status@row = "Gray", 0)))))

Paul Newcome

Smartsheet reads percentages as a portion of a whole. 1 = 100%. 0.50 = 50%. So on and so forth. Try adjusting the formula to output numbers (0 through 1 without quotes).

lora.riggs

Thanks for the response, Paul. When I try that, though, the results are unparseable. The reason being is that my formula is telling the sheet that the green status represents 100%, in the same way that I would tell an Excel sheet that Cell B2 = the value of Cell A1.

So in Excel, for example, let's say I have colors in column A. Column B is a list of numbers. I want column C to tell me what the average of the column B values is for the color listed in cells A1-A6. Does that make sense?

Paul Newcome

Right. You would only need to remove the quotes and switch over to numbers for the percentages:

=IFERROR(AVG(CHILDREN(Progress@row)), IF(Status@row = "Green", 1, IF(Status@row = "Yellow", .5, IF(Status@row = "Red", 0, IF(Status@row = "Gray", 0)))))

To average the children in the percentage column, you would remove the cell reference from the CHILDREN function.

AVG(CHILDREN())

lora.riggs

I'm confused by your comment. The formula is only being used in the percentage column. I am not using this formula in any other part of the sheet.

lora.riggs

https://community.smartsheet.com/discussion/comment/350168#Comment_350168

I'm confused by your comment. The formula is only being used in the percentage column. I am not using this formula in any other part of the sheet.

Paul Newcome

Try this:

=IFERROR(AVG(CHILDREN()), IF(Status@row = "Green", 1, IF(Status@row = "Yellow", .5, IF(Status@row = "Red", 0, IF(Status@row = "Gray", 0)))))