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
-
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)))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Answers
-
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).
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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?
-
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())
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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.
-
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.
-
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)))))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
