Avatar of Matt Pinkston
Matt Pinkston
 asked on

RAG Summary Formula

Looking for an industry standard on how to summarize RAG (Red, Amber, Green) ratings.

I have seen
Red-1
Amber-3
Green-5

(# of Red *1) + (# of Amber * 3) + (# of Green * 5) / Total #

If result = 1-2.5 then Red
If result = 2.6-3.5 then Amber
If result = ? 3.6 then Green

Microsoft ExcelMath / ScienceMicrosoft Office

Avatar of undefined
Last Comment
phoffric

8/22/2022 - Mon
Rob Henson

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.
Matt Pinkston

ASKER
There has to be a standard or best practice
Rob Henson

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.


Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Rob Henson

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"
Matt Pinkston

ASKER
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
phoffric

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

Open 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

Open 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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Matt Pinkston

ASKER
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
phoffric

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

Open 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]

Open 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.
phoffric

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]

Open 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

Open 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

Open 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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
phoffric

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.