IF Formula with Multiple Criteria

kelceyg

Hello! I have a software enhancement sheet, and I am trying assign priority to each enhancement based on the level of effort (LOE) + change priority calculated score.

The LOE can be one of the following: 1 - Unknown, 2 - High, 3 - Medium, 4 - Low, 5 - None

The change score change range from 4-25

The breakdown for priority is:

• If LOE is low, but the change score is >= 19, first priority or first wave
• If LOE is medium, but change socre >= 19, second priority
• If LOE is low or medium, and change score is 10-18, third priority
• If LOE is high, and change score >=19, fourth priority
• If LOE is high, and change score is 10-18, fifth priority
• All remaining enhancements would be sixth priority.

I started trying to create a formula for the above breakdown. However, I am not getting the expected results. What I have so far is: =IF(AND([Level of Effort]@row = "2 - High", [Change Priority - Calculated]@row <= 10), "Sixth Wave", IF(AND([Level of Effort]@row = "2 - High", [Change Priority - Calculated]@row >= 19), "Fourth Wave", "Fifth Wave")) + IF(AND([Level of Effort]@row = "3 - Medium", [Change Priority - Calculated]@row <= 10), "Sixth Wave", IF(AND([Level of Effort]@row = "3-Medium", [Change Priority - Calculated]@row >= 19), "Second Wave", "Third Wave"))

But this is what is happening:

I don't understand why it's bringing back two values when it should just be "Fourth Wave." Any advice would be greatly appreciated!

Devin Lee

You closed the IF statement and started another. It should be one continuous statement where the only time the false statement doesn't contain an IF is the last one.

=IF(AND([Level of Effort]@row = "4 - Low", [Change Priority - Calculated]@row >= 19), "First Wave", IF(AND([Level of Effort]@row = "3 - Medium", [Change Priority - Calculated]@row >= 19), "Second Wave", IF(OR([Level of Effort]@row = "4 - Low", [Level of Effort]@row = "3 - Medium"), IF(AND([Change Priority - Calculated]@row >= 10, [Change Priority - Calculated]@row <= 18), "Third Wave", "Sixth Wave"), IF(AND([Level of Effort]@row = "2 - High", [Change Priority - Calculated]@row >= 19), "Fourth Wave", IF(AND([Level of Effort]@row = "2 - High", [Change Priority - Calculated]@row >= 10), "Fifth Wave", "Sixth Wave")))))

Devin Lee

You closed the IF statement and started another. It should be one continuous statement where the only time the false statement doesn't contain an IF is the last one.

=IF(AND([Level of Effort]@row = "4 - Low", [Change Priority - Calculated]@row >= 19), "First Wave", IF(AND([Level of Effort]@row = "3 - Medium", [Change Priority - Calculated]@row >= 19), "Second Wave", IF(OR([Level of Effort]@row = "4 - Low", [Level of Effort]@row = "3 - Medium"), IF(AND([Change Priority - Calculated]@row >= 10, [Change Priority - Calculated]@row <= 18), "Third Wave", "Sixth Wave"), IF(AND([Level of Effort]@row = "2 - High", [Change Priority - Calculated]@row >= 19), "Fourth Wave", IF(AND([Level of Effort]@row = "2 - High", [Change Priority - Calculated]@row >= 10), "Fifth Wave", "Sixth Wave")))))

kelceyg

Thanks, Devin! That worked beautifully!