Calculating the "Average" RAG Status
Hi All
I currently have a series of projects that I've grouped according to Business Unit and each project has a RAG status.
I want to be able to have Smartsheet determine the overall RAG status of each Business Unit in the following way:
If 20% or more of the total number of projects is Amber (i.e. 2 / 10), then automatically, the Business Unit will be in an overall Amber state.
If 20% or more of the total number of projects is Red (i.e. 2/10), then automatically, the Business Unit will be in an overall Red state.
How would I accomplish this please?
Thanks
Best Answer
-
Hi @Safiya B,
If you've grouped things up with indentation then you can do this.
Here the formula in the project status is:
=IFERROR(IF(COUNTIF(CHILDREN(Status@row), "Red") / COUNT(CHILDREN(Status@row)) >= 0.2, "Red", IF(COUNTIF(CHILDREN(Status@row), "Yellow") / COUNT(CHILDREN(Status@row)) >= 0.2, "Yellow", "Green")), "")
I've put it in a seperate column in order to have it as a column formula, but if you only have a few lines you could equally have it in the lines for project with some copy/pasting.Hope this is of some assistance - if you've any questions etc. then let me know!
Answers
-
Hi @Safiya B,
If you've grouped things up with indentation then you can do this.
Here the formula in the project status is:
=IFERROR(IF(COUNTIF(CHILDREN(Status@row), "Red") / COUNT(CHILDREN(Status@row)) >= 0.2, "Red", IF(COUNTIF(CHILDREN(Status@row), "Yellow") / COUNT(CHILDREN(Status@row)) >= 0.2, "Yellow", "Green")), "")
I've put it in a seperate column in order to have it as a column formula, but if you only have a few lines you could equally have it in the lines for project with some copy/pasting.Hope this is of some assistance - if you've any questions etc. then let me know!
-
Hi Nick
Thanks for your reply. I've set it up similar to how you've constructed it above (indentation). Can you please tell me where I copy / paste formula into and how I ensure which cells it looks at when it calculates the formula?
-
The formula above is in the first column - it is looking at the children (i.e. indented rows) in the "Status" (3rd column).
If your sheet has different headers, just substitute the relevant name there in place of Status in the formula. If they are multiple words, they will need square brackets - for example if your Status equivalent columns is called "Task Status", then anywhere the formula is Status@row would be [Task Status]@row instead.
Hopefully this is clear enough, if not post a screenshot of your headers and I can alter the formula to suit! ☺️
-
Hi Nick
Thanks - I've attached a screen print and I wanted to add that when I put the formula in originally, I got the UNPARSEABLE error - but perhaps it was the way the columns were labeled.
-
You should be able to put the formula into any column as it is as your status column has the same label - I can't see any reason why it shouldn't work. 😊
-
Thank you so much for the help Nick - it worked :) 😁
-
No problem, happy to have helped!
-
Hi Nick,
I would like to extend the formula to account for grey items as well, please have a look at the master formula and the suggested formula and let me know where I'm missing something. Thanks!
ASIS:
=IFERROR(IF(COUNTIF(CHILDREN(Status@row), "Red") / COUNT(CHILDREN(Status@row)) >= 0.2, "Red", IF(COUNTIF(CHILDREN(Status@row), "Yellow") / COUNT(CHILDREN(Status@row)) >= 0.2, "Yellow", "Green")), "")
TOBE:
=IFERROR(IF(COUNTIF(CHILDREN(Status@row), "Red") / COUNT(CHILDREN(Status@row)) >= 0.2, "Red", IF(COUNTIF(CHILDREN(Status@row), "Yellow") / COUNT(CHILDREN(Status@row)) >= 0.2, "Yellow", IF(COUNTIF(CHILDREN(Status@row), "Green") / COUNT(CHILDREN(Status@row)) >= 0.81, "Green", "Gray")), "")
-
Nick thanks so much!!! Works perfectly.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!