Formula challenge

Phil Wightman

I want to remove the subjectivity in reporting on project domains. The aim is to use a formula to determine the 'summary rating' of a domain eg risk. This will be based on the content of the risk log (sheet).

Community help is requested on the formula.

The table below maps the thinking to develop the formula. I think it will start with 'IF"; that all I have 😥. It needs to account for all of the criteria and return a red, amber of green and a grey if all four columns are blank. Any help appreciated. Word of warning - if you solve it there are more of the same for issues, schedule, change readiness and overall!

Genevieve P.

Hi @Phil Wightman

I'd be happy to help you with this! The easiest way I find to build this out is to write out each statement and conditions separately, then add them together.

We would start with Gray if all 4 columns are blank:

=IF(AND(Rating@row = "", Likelihood@row = "", Mitigation@row = "", [Due date]@row = ""), "Gray",

Then we'll want to do the Green one. This is because when we state that the rating is either Medium or Low, it means that the formula will only look to the next instructions if this is false (so if the rating is not blank, or if it's "Critical" or "High"). This eliminates an instruction to write for the latter colours!

IF(AND(OR(Rating@row = "Medium", Rating@row = "Low"), OR(Likelihood@row = "Unlikely", Likelihood@row = "Very Unlikely"), Mitigation@row <> "", [Due date]@row <> ""), "Green",

Now we can move on to Yellow:

IF(AND(OR(Likelihood@row = "Likely", Likelihood@row = "Possible"), [Due date]@row = ""), "Yellow",

Notice that we don't have Mitigation in here at all. This is because in your description you note that Mitigation could either be filled in OR blank, so really all we need to look for is if the Due date is blank.

Finally, the Red statement:

IF(AND(Likelihood@row = "Almost Certain", [Due date]@row = ""), "Red"

For a full formula:

=IF(AND(Rating@row = "", Likelihood@row = "", Mitigation@row = "", [Due date]@row = ""), "Gray", IF(AND(OR(Rating@row = "Medium", Rating@row = "Low"), OR(Likelihood@row = "Unlikely", Likelihood@row = "Very Unlikely"), Mitigation@row <> "", [Due date]@row <> ""), "Green", IF(AND(OR(Likelihood@row = "Likely", Likelihood@row = "Possible"), [Due date]@row = ""), "Yellow", IF(AND(Likelihood@row = "Almost Certain", [Due date]@row = ""), "Red"))))

However keep in mind there are some logic instructions that are not covered. For example, what should the formula do if the Rating is "Low", but the Likelihood is "Almost Certain"? Right now, that would turn Red with the current formula.

What about if the Rating is "Critical" but the Likelihood is "Medium"? Or what if you have "Critical", with "Almost Certain", yet there's a Due Date input? In both of these instances the formula would return a blank status ball, since there are no instructions for these combinations.

I would suggest setting up a test sheet with all possible combinations and your desired output for each row. Then we can make sure the instructions take into account any possibility!

Cheers,

Genevieve

Phil Wightman

Thanks for the response @Genevieve P. Its not a rating of a single row but of all the criteria together. EG a summary rating of all the risks in the risk log. Does that make sense? My latest understanding is that the table I posted origially has 15 IF statements that would need to be listed (nested).

Genevieve P.

Hi @Phil Wightman

I'm not quite sure I understand; it sounds like perhaps a Risk Formula column like I pictured above may still be helpful? You could evaluate each row's risk status, then once you have defined the colour per-row, you can use that Risk Formula as a helper column to conduct overall formulas.

For example, you could count how many rows are Red versus total rows. Or, I would probably set up counts for each colour and display this in a Dashboard as a semi-circle Donut Chart, identifying how many tasks are associated with each colour (giving an overall colour for the whole sheet).

If this doesn't work, it would be helpful to see a screen capture of your actual sheet in Smartsheet (or your sheet structure, with sample data in place of actual data).