RAID Formula for Dashboard

vlopez47

Does anyone have a RAID log formula they can share to calculate the overall and individual project health of a project based on the amount and priority level of Risks and Issues?

Currently, I have formulas that evaluate the status of issues and risks in our RAID log which return color codes (Red, Yellow, Green) in a project dashboard based on the following conditions:

Overall Project Health:

=IF(OR(Scope@row = "R", Schedule@row = "R", Cost@row = "R", Issues@row = "R", Risks@row = "R"), "R", IF(Value27 >= 2, "Y", "G"))

Issues:

=IF(COUNTIFS({RAID Log | RAID Type}, ="Issue", {RAID Log | Status}, <>"Closed", {RAID Log | Priority}, ="High") > 0, "R", IF(COUNTIFS({RAID Log | RAID Type}, ="Issue", {RAID Log | Priority}, ="Medium") > 2, "Y", "G"))

Risks:

=IF(COUNTIFS({RAID Log | RAID Type}, ="Risk", {RAID Log | Status}, <>"Closed", {RAID Log | Risk Impact}, ="High") > 0, "R", IF(COUNTIFS({RAID Log | RAID Type}, ="Risk", {RAID Log | Risk Impact}, ="Medium") > 2, "Y", "G"))

Matt Lynn ACT

@vlopez47 I don't have them on hand ready to go but I have many times done one that takes a risk value and severity value and aggregate that into some average or weighted average based on how many are not closed out. Each item would have a risk and severity score (maybe 1-5 or stars or something) then maybe those are weighted averages, then total that up or filter out the ones that are closed into a overall average. It's custom with every situation / client.