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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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!

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

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

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!