Returning 1 or 0, based on multiple conditions.

Mohammed Sulaiman

I'm trying to return a value 1 if the following conditions are met,

1. Expected % Complete < 40%, and Expected % Complete - Actual % Complete > 25%
2. Expected % Complete > 40%, and Expected % Complete < 60%, Expected % Complete - Actual % Complete > 20%
3. Expected % Complete > 60%, and Expected % Complete < 85%, Expected % Complete - Actual % Complete > 10%
4. 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.

Genevieve P.

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

Genevieve P.

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

Mohammed Sulaiman

Thanks, that worked perfect.

Genevieve P.

Great! Happy to help.