Welcome to the Smartsheet Forum Archives


The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

Programme RAG

Vikki Cooper
edited 12/09/19 in Archived 2017 Posts

Hi All,

 

I have a roll up sheet for the programme of work, each line is a project that is being worked on or queued for work. Each line has an Overall RAG called ''Overall Status''' for the overall project and I have a metrics table which states the Programme RAG, currently this RAG for the Programme would be manual, i would prefer if the system would count how many Red, Yellow, Green, Blue and Not Started and calculated a Programme RAG from this.

Can anyone help?

Thanks

Comments

  • L_123
    L_123 ✭✭✭✭✭✭

    not entirely sure if I fully understood what you were asking, but take a look at this. Hopefully it can help.

     

    https://app.smartsheet.com/b/publish?EQBCT=9d84d3d2a0134e5482b2f0160309f049

     

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 07/18/17

    I think I misunderstood, that happens a lot haha. If you are looking to assign a graded RAG based on the other RAG you need to assign a percentage to each color, count the color and multiply by the percentage, then sum them and divide by the total count. Honestly you shouldn't copy a formula and instead build it to fit you own needs, if you copy one it isn't going to be as effective in addressing what you want it to do. That said, if I understood you correctly this time, this is what I would output.

    =IF((COUNTIF(CHILDREN($RAG$1), "Red") * 0.3 + COUNTIF(CHILDREN($RAG$1), "Yellow") * 0.7 + COUNTIF(CHILDREN($RAG$1), "Green") + COUNTIF(CHILDREN($RAG$1), "Blue") * 0.5) / (COUNT(CHILDREN(RAG1)) + COUNTIF(CHILDREN(RAG1), ISBLANK(@cell))) > 0.9, "Green", IF((COUNTIF(CHILDREN($RAG$1), "Red") * 0.3 + COUNTIF(CHILDREN($RAG$1), "Yellow") * 0.7 + COUNTIF(CHILDREN($RAG$1), "Green") + COUNTIF(CHILDREN($RAG$1), "Blue") * 0.5) / (COUNT(CHILDREN(RAG1)) + COUNTIF(CHILDREN(RAG1), ISBLANK(@cell))) > 0.6, "Yellow", IF((COUNTIF(CHILDREN($RAG$1), "Red") * 0.3 + COUNTIF(CHILDREN($RAG$1), "Yellow") * 0.7 + COUNTIF(CHILDREN($RAG$1), "Green") + COUNTIF(CHILDREN($RAG$1), "Blue") * 0.5) / (COUNT(CHILDREN(RAG1)) + COUNTIF(CHILDREN(RAG1), ISBLANK(@cell))) > 0, "Red", "Blue")))

    looks fun huh? anyway in that formula I assigned the following percentages (honestly at random I just picked numbers)

    R=30% (red grade given between 0 & 70%)

    Y=70% (yellow grade given between 70 & 90%)

    G=100% (green grade given between 90 & 100%)

    Blue=50% (blue grade given at 0%)

    Blank=0%

    I also posted it in my previously shared sheet to show you how it works.

     

    (I corrected a small error via edit, there was a glitch with the blanks that has been fixed)

  • Hi Luke, 

    Thanks for you hard work i appreciate it, but is there any easier way to do this?

    This sheet is for my Vice President to use and if i start adding in lots of columns he may not use it.

    Here is a link to copy of my sheet, which i have desensitised. https://app.smartsheet.com/b/home?lx=BIovARTf5u59CrP96JZk5A

    ''Program Health'' is where i want the average to show rather than me making a judgement call each month, plus it will update each time someone updates a project RAG.

    ''Overall Status'' is where i want it to get the information to make its calculation

    Thanks so much

    Vikki

  • L_123
    L_123 ✭✭✭✭✭✭

    each column that i built was independently based off the information column so they could all exist without each other so you don't need extra columns. That said, you have a problem that is a little harder to solve than it looks at first reading. The RAG is isn't particularly difficult, the blue however, becomes difficult with how the program works. If you want to post blue if there are any blanks and you are adding/removing rows constantly you are going to need someone who is better at formulas than I. I could write something quick that would work until you started adding/removing rows, but I don't think you want that. I posted the working RAG formula in your worksheet and below. I recommend you get with J. Craig Williams.

    =IF(AND(COUNTIF([Overall Status]:[Overall Status], "Green") > COUNTIF([Overall Status]:[Overall Status], "Red"), COUNTIF([Overall Status]:[Overall Status], "Green") > COUNTIF([Overall Status]:[Overall Status], "Yellow")), "Green", IF(AND(COUNTIF([Overall Status]:[Overall Status], "Yellow") > COUNTIF([Overall Status]:[Overall Status], "Green"), COUNTIF([Overall Status]:[Overall Status], "Yellow") > COUNTIF([Overall Status]:[Overall Status], "Red")), "Yellow", IF(AND(COUNTIF([Overall Status]:[Overall Status], "Red") > COUNTIF([Overall Status]:[Overall Status], "Green"), COUNTIF([Overall Status]:[Overall Status], "Red") > COUNTIF([Overall Status]:[Overall Status], "Yellow")), "Red")))

  • L_123
    L_123 ✭✭✭✭✭✭

    Something you could do though that would be rather easy is you could add a hidden helper column with a conditional if statement. so put the column right next to overall status name it FormAssist and put this in every row:

     

    If(isblank([Overall Status]1),"error")

    then you can draw on that for your formula. add an if statement to the beginning

    if(countif(FormAssist:FormAssist,"error")>0,"blue",*post the large formula here*)

  • L_123
    L_123 ✭✭✭✭✭✭

    I added that to your sheet, and added another caveat to the formula. I changed 

    if(isblank([Overall Status]1),"Error") to

    =IF(AND(ISBLANK([Overall Status]1), ISTEXT([Project Plan]1)), "error")

    I left the column unhidden, but I would hide it before implementation if you use this approach.

  • Ahh brilliant, thank you so much for your help! I am not very good with formulas other than a simple =sum :) you are a definite credit to the community!!

This discussion has been closed.