IF Formula with Multiple Criteria
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 425
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 1018, third priority
 If LOE is high, and change score >=19, fourth priority
 If LOE is high, and change score is 1018, 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 = "3Medium", [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!
Best Answer

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")))))
Answers

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")))))

Thanks, Devin! That worked beautifully!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.6K Get Help
 63 Global Discussions
 67 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!