Calculating the "Average" RAG Status

Safiya B

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

Nick Korna

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!

Nick Korna

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!

Safiya B

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?

Nick Korna

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! ☺️

Safiya B

Capture.PNG

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.

Nick Korna

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. 😊

Safiya B

Thank you so much for the help Nick - it worked :) 😁

Nick Korna

No problem, happy to have helped!

Safiya B

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

Jonathan_p_o_ITM

Nick thanks so much!!! Works perfectly.