Returning 1 or 0, based on multiple conditions.
I'm trying to return a value 1 if the following conditions are met,
- Expected % Complete < 40%, and Expected % Complete - Actual % Complete > 25%
- Expected % Complete > 40%, and Expected % Complete < 60%, Expected % Complete - Actual % Complete > 20%
- Expected % Complete > 60%, and Expected % Complete < 85%, Expected % Complete - Actual % Complete > 10%
- Expected % Complete > 85%, Expected % Complete - Actual % Complete > 5%
For any other condition, the cell would return 0.
The column, will be checkbox column, and it will check At Risk task, when the number is 1.
Formula Used:
=IF(OR(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.25, [Expected % Complete]@row < 0.4), IF(OR(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.20, [Expected % Complete]@row > 0.4, [Expected % Complete]@row < 0.6), IF(OR(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.1, [Expected % Complete]@row > 0.6, [Expected % Complete]@row < 0.85), IF(OR(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.05, [Expected % Complete]@row > 0.85) 1, 0)))))))))
Thanks for your suggestions.
Best Answer
-
Hi Mohammed,
It looks like you don't need to have any OR statements in there, as you want to return 1 when each of the criteria are met. For example, if you say if the Complete cell is over 40% OR under 60%, this covers every percent. Your AND statement will cover this.
You will also need to tell each individual IF statement to return 1, instead of having this rule only at the end.
Try this:
=IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.25, [Expected % Complete]@row < 0.4), 1, IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.2, [Expected % Complete]@row > 0.4, [Expected % Complete]@row < 0.6), 1, IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.1, [Expected % Complete]@row > 0.6, [Expected % Complete]@row < 0.85), 1, IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.05, [Expected % Complete]@row > 0.85), 1, 0))))
Broken Down:
1.
=IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.25, [Expected % Complete]@row < 0.4), 1,
2.
IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.2, [Expected % Complete]@row > 0.4, [Expected % Complete]@row < 0.6), 1,
3.
IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.1, [Expected % Complete]@row > 0.6, [Expected % Complete]@row < 0.85), 1,
4.
IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.05, [Expected % Complete]@row > 0.85), 1, 0))))
Let me know if you have any questions, or if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Hi Mohammed,
It looks like you don't need to have any OR statements in there, as you want to return 1 when each of the criteria are met. For example, if you say if the Complete cell is over 40% OR under 60%, this covers every percent. Your AND statement will cover this.
You will also need to tell each individual IF statement to return 1, instead of having this rule only at the end.
Try this:
=IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.25, [Expected % Complete]@row < 0.4), 1, IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.2, [Expected % Complete]@row > 0.4, [Expected % Complete]@row < 0.6), 1, IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.1, [Expected % Complete]@row > 0.6, [Expected % Complete]@row < 0.85), 1, IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.05, [Expected % Complete]@row > 0.85), 1, 0))))
Broken Down:
1.
=IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.25, [Expected % Complete]@row < 0.4), 1,
2.
IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.2, [Expected % Complete]@row > 0.4, [Expected % Complete]@row < 0.6), 1,
3.
IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.1, [Expected % Complete]@row > 0.6, [Expected % Complete]@row < 0.85), 1,
4.
IF(AND([Expected % Complete]@row - [Actual % Complete]@row > 0.05, [Expected % Complete]@row > 0.85), 1, 0))))
Let me know if you have any questions, or if this works for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Thanks, that worked perfect.
-
Great! Happy to help.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives