How can I average a percentage in a parent row?

lora.riggs · 11/01/22

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 · 11/14/22

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 · 11/01/22

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 · 11/05/22

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 · 11/07/22

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 · 11/14/22

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 · 11/14/22

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 · 11/14/22

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

How can I average a percentage in a parent row?

Best Answer

Answers

Help Article Resources

Categories