Using OR/And Function
I am trying to create a formula for the following:
45 days before the deadline – where work is completed less than 65% - Red alert
65-75 % - Yellow Alert
75% more – Green Alert
I am not able to combine two Parameters to determine the colours.
See an example
=IF(OR(AND([Revised Planned Finish]@row - [Revised Planned Finish]9 > 45, [% Complete]9 > 0.75, "Green")))
I keep on getting #incorrect argument set
Kindly advise
Best Answer
-
Hey @Avinash Chandra
=IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]9 < 45, [% Complete]9<0.65), "Red", IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]9 < 45, [% Complete]9<0.75, "Yellow", IF(OR(AND([Revised Planned Finish]@row - [Revised Planned Finish]9 < 45, [% Complete]9>=0.75), [% Complete]9=1), "Green")
Note the only OR that is needed is the one I added in your Green criteria. I assumed it would be Green if your %Complete went to 100% at any time.
I swapped you greater than 45 days to less than 45 days - I believe this is what you meant. I also noticed you called out a specific row number which will prevent you from turning this into a column formula. Depending on your data layout, you may be able to find workarounds that allow you to use a column formula.
Will the formula above work for you?
Kelly
Answers
-
Hey @Avinash Chandra
=IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]9 < 45, [% Complete]9<0.65), "Red", IF(AND([Revised Planned Finish]@row - [Revised Planned Finish]9 < 45, [% Complete]9<0.75, "Yellow", IF(OR(AND([Revised Planned Finish]@row - [Revised Planned Finish]9 < 45, [% Complete]9>=0.75), [% Complete]9=1), "Green")
Note the only OR that is needed is the one I added in your Green criteria. I assumed it would be Green if your %Complete went to 100% at any time.
I swapped you greater than 45 days to less than 45 days - I believe this is what you meant. I also noticed you called out a specific row number which will prevent you from turning this into a column formula. Depending on your data layout, you may be able to find workarounds that allow you to use a column formula.
Will the formula above work for you?
Kelly
-
Hi Kelly,
Thanks for your quick response. This worked for me.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!