Programme RAG

Vikki Cooper

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

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

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)

Vikki Cooper

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

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

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

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.

Vikki Cooper

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