RAG Summary Formula

I don't believe there is an Industry Standard as all industries will have different interpretations of what is Good, Neutral or Bad.

For example, payment of invoice dates:

On time - Good so green or could be Neutral so amber with paid early being only criteria for Good
Late - how late is late? One week, one month? All depends on the company's cash flow or customer credit agreements.

There has to be a standard or best practice

Reading again, I think you mean a way of getting an overall RAG position rather than individual RAG rating.

Your suggestion of weighting the number of each by multiplying by 1/3/5 could have the opposite effect than desired; over weighting the number of Greens could overshadow the number of Reds and give a false impression.

Weighting the other way however could also highlight an issue when there isn't really one.

Why does there have to be an Industry Standard? It is all very subjective and dependent on the industry or size of company.

There is an Industry Standard for monitoring progress, there is actually more than one but one that I am familiar with is Earned Value Management (EVM).

EVM looks at the Cost and Schedule of a project as related factors rather than individual metrics.

EVM calculates a Cost Index and a Schedule Index using various elements of Cost and Schedule.

Standard Cost Performance just looks at cost to date compared to Budget
Standard Schedule Performance just looks at whether a project is on time.

EVM uses both, for example the cost to date could be more than Budget but could also be ahead of schedule.

There are numerous websites that give more detail and companies that offer training.

Other monitoring processes can be found if you just Google "Program Management methods"

okay maybe not an industry standard but there has to be a methodology that people lean towards.

If there is no weighting on one question over another and there are 5 red, 8 yellow and 12 green how is the best way to come up with the average in a color rating

I am not saying my formulations represent a best practice. I am just working out a formula if I were asked to come up with one with little research into best practices.

Scenario: 5 red, 8 yellow and 12 green
Here is one possible way to compute shades of Red, Yellow, Green.

RAG Number = 
(-1.0*nrRed - 0.5*nYellow + 1.0*nGreen) / (nrRed + nYellow + nGreen) = 
(-1.0*5 - 0.5*8 + 1.0*12)/( 5 + 8 + 12) = (-5 - 4 + 12)/25 = 3/25 = 0.12

Select allOpen in new window

If all items are green, then below RAG score = 1.0.
If all items are red, then below RAG score = -1.0.
If all items are orange, then below RAG score = -0.5.
RAG scores near 0 indicate roughly an even mixture of R,Y,G. - indicating that some improvement needs to be done.

If you must have scores of 1,3, 5, then since 0.12 is closer to -0.5 (orange) than to 1.0 (Green), then for your scenario, the score is Orange (3). However, having a single number for a large project surely hides a lot of details. Breaking up the project into somewhat independent divisions, and scoring individually, would shed more light on the division team's performance.

Now, just like some athletes or politicians who hate losing more than winning, a company who feels its reputation, or good will, is damaged more severely with negative scores may weigh the coefficients to emphasize, not losing. For example:

RAG Number = 
(-2.0*nrRed - 0.75*nYellow + 1.0*nGreen) / (nrRed + nYellow + nGreen) = 
(-2.0*5 - 1.0*8 + 1.0*12)/( 5 + 8 + 12) = (-10 -8 + 12)/25 = -6/25 = -0.24

Select allOpen in new window

With this formulation, the RAG Number can be in the range -2.0 to +1.0.
This harsher treatlment may seem to be unreasonable; yet, it is quite the norm. In annual reviews, employees who have performed well but have made a couple a Red mistakes get such poor reviews that they resign, if not laid off. And in the stock market, high flying well-performing stocks get hammered severely if they only performed remarkably well instead of extraordinarily well.

so using the below
RAG Number =  
(-1.0*nrRed - 0.5*nYellow + 1.0*nGreen) / (nrRed + nYellow + nGreen) =  
(-1.0*5 - 0.5*8 + 1.0*12)/( 5 + 8 + 12) = (-5 - 4 + 12)/25 = 3/25 = 0.12
What range is red
What range is yellow
What range is green

>> What range is red, yellow, green?
In general, let Wr < Wy < Wg be the three weighting factors for RED, YELLOW, GREEN.
The midpoint between two weighting factors can be the demarcation scheme.

RAG Number =  
(Wr *nRed + Wy *nYellow + Wg *nGreen) / (nRed + nYellow + nGreen)
Midpoint_RY = (Wr + Wy) / 2
Midpoint_YG = (Wy + Wg) / 2

Select allOpen in new window

In our example, Wr = -1.0, Wy = -0.5, Wg = 1.0. Using these coefficients, and the midpoint scheme, the demarcations are

RED: [-1.0 .. -0.75]
YELLOW: (-0.75 .. +0.25]
GREEN: (+0.25 .. +1.0]

Select allOpen in new window

where a bracket [ or ] means "include in range"
and parenthesis means "exclude from range";
so that a -0.75 means RED, and a 0.25 means YELLOW.
The choice of using parenthesis or brackets as well as the coefficients, are, of course, yours to make.

If you prefer a more even distribution, you could make Wy = 0. But that still isn't quite what I want for even distribution. Will try to come back with a better set of coefficients so that each category (R,Y,G) have an equal opportunity based on the rag numerical score.

If you want an even distribution of outcomes for RYG, then you can work with the following:

Wr = -3.0;  Wy = 0.0;  Wg = +3.0;
RED: [-3.0 .. -1.0]
YELLOW: (-1.0 .. +1.0]
GREEN: (+1.0 .. +3.0]

Select allOpen in new window

For this scheme, the function looks like:

function [rag_score, RAG_Result] = RAG(Wr, Wy, Wg, nR, nY, nG)
    rag_score = (Wr *nR + Wy *nY + Wg *nG) / (nR + nY + nG);
    if rag_score <= -1
         RAG_Result = 'RED';
    elseif rag_score <= 1
         RAG_Result = 'YELLOW';
    else
         RAG_Result = 'GREEN';
    end
end

Select allOpen in new window

Executing the function results:

[Wr, Wy, Wg] = [-3, 0, 3]
[nR, nY, nG] = [5, 8,12]
[rag_score, RAG_Result] = RAG(Wr, Wy, Wg, nR, nY, nG)
rag_score = 0.8400
RAG_Result = YELLOW

Select allOpen in new window

If nY=0 (with nR=5 and nG=12, as before), then rag_score = 1.2353 and RAG_Result = GREEN. Adding the 8 Yellow items to the mix brought that rag_score down closer to 0, enough so, to turn the GREEN result to an YELLOW result.