Calculating the "Average" RAG Status

Options

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 ✓
    Options

    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 ✓
    Options

    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
    Options

    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 ✭✭✭✭✭✭
    Options

    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
    Options

    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 ✭✭✭✭✭✭
    Options

    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
    Options

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

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    No problem, happy to have helped!

  • Safiya B
    Options

    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
    Options

    Nick thanks so much!!! Works perfectly.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!