Help with If formula using Icons
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")))
Answers
-
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:
- First
IF
condition:- If
[Resource Utilization]@row > 0.9
, it returns "No".
- If
- Second
IF
condition (within theAND
):- 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
.
- If
- Third
IF
condition:- If
[Resource Utilization]@row <= 0.5
, it returns "Yes".
- If
Hope this helps
Marcé Holzhauzen
Solution and Delivery Consultant with Award Winning Partner
Want to meet?Check my availabilitywww.prodactive.co.uk
If this helped, help me & the SSC by accepting it and reacting w/💡insightful, ⬆️ Vote Up, and/or ❤️Awesome. - First
-
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).
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!