Forum Discussion
Solved
Better Alternative for a Nested IF with multiple Columns in a Logical Test?
The code below works, but I hear using nested IF's is not the most elegant way to go. I am not a programmer, so I need a bit of advice. Is there a better way to achieve what I am doing, or is it simple enough to keep it as is? I would have used SWITCH, but i have to run a logical test against two columns in two of those IF statements, and I don't believe SWITCH handles that.
Milestone Saturation = IF(Tasks[Milestones Lead (Days)] > 0 && Tasks[TaskPercentCompleted] = 100,"Complete", IF(Tasks[Milestones Lead (Days)] > 0 && Tasks[TaskPercentCompleted] = 0,"Late", IF(Tasks[Milestones Lead (Days)] = 0, "Current", IF(Tasks[Milestones Lead (Days)] < 0 && Tasks[Milestones Lead (Days)] >= - 3, "Upcoming", IF(Tasks[Milestones Lead (Days)] < -3, "Future" )))))
Thanks!
try this:
Milestone Saturation = SWITCH( TRUE(), [Milestones Lead (Days)] > 0, SWITCH( TRUE(), [TaskPercentCompleted] = 100, "Complete", [TaskPercentCompleted] = 0, "Late" ), [Milestones Lead (Days)] = 0, "Current", [Milestones Lead (Days)] < 0, SWITCH( TRUE(), Tasks[Milestones Lead (Days)] >= - 3, "Upcoming", Tasks[Milestones Lead (Days)] < -3, "Future" ) )
3 Replies
Peter_RResolver II
try this:
Milestone Saturation = SWITCH( TRUE(), [Milestones Lead (Days)] > 0, SWITCH( TRUE(), [TaskPercentCompleted] = 100, "Complete", [TaskPercentCompleted] = 0, "Late" ), [Milestones Lead (Days)] = 0, "Current", [Milestones Lead (Days)] < 0, SWITCH( TRUE(), Tasks[Milestones Lead (Days)] >= - 3, "Upcoming", Tasks[Milestones Lead (Days)] < -3, "Future" ) )
Looks good! Thank you! I am assuming that this is a more efficient way, and that this can be called a "Nested Switch" :)
- Peter_R
You could call it that.
I should have given credit to the idea in my first reply that it was from a posting I recalled seeing from Rob Collie - https://powerpivotpro.com/2015/03/the-diabolical-genius-of-switch-true/
