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

edited 12/09/19

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

Tags:

• ✭✭✭✭✭✭

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

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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*)

• ✭✭✭✭✭✭