Text formula based on multiple column criteria

Robert Hawkey

I am trying to work out a formula based on three columns:

Est. Duration in Months

Months to Release

Scope

Est. Duration in Months is a single number based on a formula that calculates the complexity of a project.

Months to Release calculates how long from today until a specified release date, in months.

Scope is trying to indicate RED, YELLOW, GREEN status based on the delta between those two previous dates. For example, if Est. Duration is 12 months, and Duration to Release is 8 months, that would flag as RED. If they were the same, it would flag as YELLOW, and if Duration to Release was larger it would flag as green.

The problem I'm having is that even when they are equal, it still flags it as GREEN. Here is my formula:

=IF([Est. Duration in Months]@row > [Months to Release]@row, "RED", IF([Est. Duration in Months]@row = [Months to Release]@row, "YELLOW", IF([Est. Duration in Months]@row < [Months to Release]@row, "GREEN")))

So far I can only get GREEN and RED to populate correctly. The = formula is not working. Seems to be overwritten by the < formula. Any help would be appreciated. Thanks!

I have attached a screenshot here. NOTE the colorization is coming from conditional formatting as I wanted it to be a color, and not just say "GREEN". The underlined should in theory be making the "Scope" cell next to them YELLOW.

Kelly Moore

Hello @Robert Hawkey

The syntax of your formula appears correct. Have you tried disabling your conditional formatting to see what values the formula actually returns? Also, have you tried swapping the order of how your conditional formulas appear in their list - I mean slide the green rule below the yellow rule.

Does the formula actually return the correct value?

Kelly

Sarah Donnelly

Hi @Robert Hawkey

First, I must state that I am not a formula guru but in most 'health' formulas I have used, the third condition, which exists always when the other two are false, does not get fully defined. Save your current syntax of course before trying my suggestion but try this variation and see if it helps.

=IF([Est. Duration in Months]@row > [Months to Release]@row, "RED", IF([Est. Duration in Months]@row < [Months to Release]@row, "GREEN", "Yellow”)))