Updating a status column based on Values in Expected % Complete and % Complete

Mohammed Sulaiman

I have dates from two columns, Expected % Complete, and % Complete which I want to use, and populate the Status Column (Dropdown) using Formulas:

The Dropdown options are " Not Started", "Behind", "On Track", and "Complete". I tried using the below formula

=IF(AND([% Complete]@row = "100%", ([Expected % Complete]@row < "100%")),"Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > "0%")), "On

Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > "0%")),

"Behind", "Not Started")))

How to fix this ?

Genevieve P.

Hi Mohammed,

The structure of this is great, well-done! However, when referencing a number column that has the % symbol turned on, you want to reference percentages as decimals.

For example, instead of saying "100%", you would write the number 1.

Try this:

=IF(AND([% Complete]@row = 1, ([Expected % Complete]@row < 1)), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > 0)), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > 0)), "Behind", "Not Started")))

You can read more about this on our Formula FAQ page (click here).

Cheers!

Genevieve

Mohammed Sulaiman

Hi Genevieve,

I did try the above solution, and it worked. Thanks for your help.

However, I added another condition, where the status becomes "Not Started/Behind".

=IF([% Complete]@row = 1, ([Expected % Complete]@row < 1)), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, ([% Complete]@row > 0)), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row > 0)), "Behind", IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row = 0)), "Not Started/Behind", "Not Started"))).

This additional condition " IF(AND([% Complete]@row < [Expected % Complete]@row, ([% Complete]@row = 0)), "Not Started/Behind" " is to show items, that are supposed to start but are behind, as the Expected % Complete > 0, and % Complete = 0.

However, when I add this formula, the "Not Started" status doesn't populate as it should, when the Expected % Complete = 0, % Complete = 0. Instead the "Not Started/Behind" populates in the cell.

How to fix this ?

Thanks,

Genevieve P.

Hi Mohammed,

This formula has a number of extra closing parentheses that should not be there. When you use "AND" you don't need to add more parenthesis to close of different instructions. Additionally, when you "nest" IF statements together in a row, leave the last parenthesis to be closed off at the very end of the entire formula.

Try this instead:

=IF(AND([% Complete]@row = 1, [Expected % Complete]@row < 1), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, [% Complete]@row > 0), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row > 0), "Behind", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row = 0), "Not Started/Behind", "Not Started"))))

AmyD

@Genevieve P. I tried this formula, and When I have a task that is Completed, showing 100% in the % complete, and the expected completion is also 100%, it returns "On Track" in the status column, vs "Complete", any suggestions for correcting this?

Genevieve P.

Hi @Amy D

Great catch! You'll want to add in a statement that says if they're both 1, return "Complete".

=IF(OR(AND([% Complete]@row = 1, [Expected % Complete]@row = 1), AND([% Complete]@row = 1, [Expected % Complete]@row < 1)), "Complete", IF(AND([% Complete]@row >= [Expected % Complete]@row, [% Complete]@row > 0), "On Track", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row > 0), "Behind", IF(AND([% Complete]@row < [Expected % Complete]@row, [% Complete]@row = 0), "Not Started/Behind", "Not Started"))))

Cheers,

Genevieve