Help with If formula using Icons

Mike Norman

I need your help to show status as Yes, No, Hold using this criteria:

No - 90% +

Hold - 51% - 89%

Yes 0% - 50%

This is my current formula, where the 'Yes' and 'No' icons show up as intended, but those that fall between 51% - 89% are showing the red X icon for No, not Hold.

=IF([Resource Utilization]@row > 0.9, "No", IF([Resource Utilization]@row > 0.89, "Hold", IF([Resource Utilization]@row, <0.5, "Yes")))

MarceHolzhauzen

Hi @Mike Norman

The IF formula is a little temperamental in that it reads from left to right UNTILL IT FINDS A TRUE statement and then stops.
- So often we need to write the formula in the opposite sequence of what we normally would. The trick is to start with the argument that will be true at the very end of the process OR a better way is
- Be very clear in specifying what needs to happen

So try,
=IF([Resource Utilization]@row > 0.9, "No", IF(AND([Resource Utilization]@row >= 0.51, [Resource Utilization]@row <= 0.89), "Hold", IF([Resource Utilization]@row <= 0.5, "Yes")))

Explanation:

1. First IF condition:
   • If [Resource Utilization]@row > 0.9, it returns "No".
2. Second IF condition (within the AND):
   • If [Resource Utilization]@row is between 0.51 and 0.89 (inclusive), it returns "Hold".
   • The AND function checks both conditions: >= 0.51 and <= 0.89.
3. Third IF condition:
   • If [Resource Utilization]@row <= 0.5, it returns "Yes".

Hope this helps

Mike Norman

Thank you for the explanation, that make sense in how it reads the formula.

The one challenge is that we have a result at 51% and it still does not display the correct icon - shows up as the 'no' (screen shot below).