Create a Multi Criteria RAG Rating Forumla

[Carling73]

Morning All

I'm looking for help in creating a formula to help create an overall RAG status based on multi criteria Risk Indicators being present or not. Where each Risk Indicator has both a standalone rating and could be positively or negatively affected by other indicators being present. I'm looking to create a formula that assess's the combination of indicators present and then returns an overall RAG rating based on the highest severity indicator present.

I have drafted up a dummy spreadsheet will attempt to attach as an example. EDIT: Apologies struggling to upload the spreadsheet! Any ideas

RAG Rating.png

Thank You

Carling73

[BadlySpelledBuoy]

To attach a file, click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

BSB

[Carling73]

Cheers BSB, appreciated

[BadlySpelledBuoy]

I'm not sure I fully understand the requirement here.
For example, why in your first example under column E does tripped No = Green, but in your second example the same one is tripped Yes = Green??

I think we'll need a fuller explanation of what's doing what here.

BSB

[Carling73]

Ok thanks BSB.

So the table at the top shows all the variables of how the indicators correlate to each other. Even if some of the indicators are tripped the outcome/RAG rating could still be green. i.e. the "E" indicator on it's own if tripped the overall outcome would still be green.

However if lets say "E" and "F" were tripped then the overall outcome would become Amber as the table shows cells G6 and F7 "E" & "F" = Amber

If for example we had "E", "F" and "G" indicators tripped then the overall outcome would be become Green again as "G" is kind of a mitigant/positive indicator and turns the Amber risk of "F" Green again as cell H7 shows

I guess what i'm trying to create is a formula in cell L15 that reads B16:K16 and interprets the table to provide an overall RAG Status

Hope that helps BSB?

[BadlySpelledBuoy]

So if A & B were triggered but not C then you'd get an Amber RAG?

I think I get the idea of it, and if all you had to do was give the overall RAG rating in L15 based on the individual scores in B16:K16 then that would be simple enough, however, I presume you're trying to automate the calculation of those individual scores too and that's where I'm drawing a blank on how to calculate it.
It would be possible with some hidden helper cells...

BSB

[Carling73]

You're spot on BSB!

Happy to take a steer on using "hidden helper cells" or any other ideas of how to achieve the same result

[BadlySpelledBuoy]

OK, I have no idea if this fits every possible scenario so you'll have to test it out a little and let me know.

Calculated via some helper cells that can be hidden away.

The blue ones work out which have Y's and the pink cells work out the rag rating for each combination.

Then the overall cell shows red if the count of blue cells = R is > 0, amber if the count of blue cells = A is >0 and so on.

Give it a try and let me know if it works for you.

I'm sure there's an easier solution, but I've been helping people out with Excel since 4:30am and my brain has given up the ghost now...

Time for beer

BSB

[Carling73]

Top man BSB really appreciated, i'll have a play and let you know. Enjoy your beer man !

[Carling73]

BSB looks good thanks, when there's multiple indicator it seems to work ok, however if there's just one it always shows as GREEN as the outcome

So for example "A" and "C" tripped as per your updated spreadsheet then it shows RED, which is spot on
"C" tripped in isolation this shows in cell c16 as red but still shows as GREEN overall in L15

The same applies whenever just one indicator is showing as "Y" in line 15

Any ideas?

[BadlySpelledBuoy]

Try this slightly amended version.

BSB