How do you enter a negative % Complete into a formula.

Brad Lanning

We are using % Complete column to drive our Status column value. I have tried -1 and -2 but not sure how to determine what % Complete should be in order for us to change the Status value to Archive or Backlog.

Any ideas?

My formula looks like this:

=IF(ISBLANK([% Complete]@row), "",

IF([% Complete]@row = -2, "Archive", 

IF([% Complete]@row = -1, "Backlog", 

IF([% Complete]@row = 0, "1. Approved", 

IF([% Complete]@row = 1, "8. Completed", 

IF(AND([% Complete]@row > 0, [% Complete]@row < 0.25, Category@row <> "Discovery"), "3. Discovery", 

IF(AND([% Complete]@row > 0, [% Complete]@row < 0.65, Category@row <> "Discovery"), "4. Development", 

IF(AND([% Complete]@row > 0, [% Complete]@row >= 0.65, Category@row <> "Discovery"), "6. Testing", 

IF(AND([% Complete]@row > 0, [% Complete]@row < 1, Category@row = "Discovery"), "3. Discovery"

)

)

)

)

)

)

)

)

)

Genevieve P.

Great, I'm glad you found a solution! 🙂

Genevieve P.

Hi @Brad Lanning

Would you mind clarifying the question? Your formula looks good!

If your % Complete column is set with Percentage formatting, this means that it needs to be referenced as decimals in your formula, which you've done. (See: Work With Percentages in Formulas)

This then means that -1 in your formula represents -100% and -2 represents -200%. Is that what you were looking to clarify?

Cheers!

Genevieve

Brad Lanning

Hello. Thanks for your response. I got it working now. I just need to enter -100 and -200 rather than -1 and -2 for the %Complete.

Genevieve P.

Great, I'm glad you found a solution! 🙂