issue applying conditional formatting to "% Complete" column

I am trying to write a formula that will change the symbol in the status column based on the percentage, as indicated by the "Complete" column.

I cannot get my sheet to respond to any of my parameters.

I have tried these formulas:

=IF(Complete@row < 30, "Red", IF(Complete@row < 60, "Yellow", IF(Complete@row = 100, "Green")))

=IF(Complete@row < 30%, "Red", IF(Complete@row < 60%, "Yellow", IF(Complete@row = 100%, "Green")))

=IF(Complete@row < "30%", "Red", IF(Complete@row < "60%", "Yellow", IF(Complete@row = "100%", "Green")))

I imagine this has something to do with the "%" sign because the formula that I used for the "Difference" column which was a dollar figure worked fine

Screen Shot 2019-12-22 at 3.02.01 PM.png

Find more posts tagged with

Accepted answers

Connor Hartford

Sam,

Instead of 60% use 0.6.

Your formula should look like this:

=IF(Complete@row < 0.3, "Red", IF(Complete@row < 0.6, "Yellow", IF(Complete@row = 1, "Green")))

Also, what should happen with values 60%-99%? With your current formula, if the value is 60%-99%, you'll just get a blank cell, is that what you want?

Andrée Starå

Hi Sam,

Percentage is writen as 1 = 100%, 0,5 is 50% and so on. Or 0.5 and so on depending on your region.

Here's an example. It's important that the IF is in the correct order.

Try something like this.

=IF(Complete@row = 1; "Green"; IF(Complete@row < 0,3; "Red"; IF(Complete@row < 0,6; "Yellow")))

The same version but with the below changes for your and others convenience.

=IF(Complete@row = 1, "Green", IF(Complete@row < 0.3, "Red", IF(Complete@row < 0.6, "Yellow")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

I hope that helps!

Have a fantastic weekend & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer. It will make it easier for others to find a solution or help to answer!

All comments

Connor Hartford

Sam,

Instead of 60% use 0.6.

Your formula should look like this:

=IF(Complete@row < 0.3, "Red", IF(Complete@row < 0.6, "Yellow", IF(Complete@row = 1, "Green")))

Also, what should happen with values 60%-99%? With your current formula, if the value is 60%-99%, you'll just get a blank cell, is that what you want?

Andrée Starå

Hi Sam,

Percentage is writen as 1 = 100%, 0,5 is 50% and so on. Or 0.5 and so on depending on your region.

Here's an example. It's important that the IF is in the correct order.

Try something like this.

=IF(Complete@row = 1; "Green"; IF(Complete@row < 0,3; "Red"; IF(Complete@row < 0,6; "Yellow")))

The same version but with the below changes for your and others convenience.

=IF(Complete@row = 1, "Green", IF(Complete@row < 0.3, "Red", IF(Complete@row < 0.6, "Yellow")))

Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

Did it work?

I hope that helps!

Have a fantastic weekend & Happy Holidays!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Sam Marritz

Hi Conner, thanks for your response!

I want status to be red if the %complete is 0-30%

Status should be yellow if %Complete is 31 - 99%

status should be red if %Complete is 100%

here is my current formula:

=IF(Complete@row < 0.3, "Red", IF(Complete@row <= 0.9, "Yellow", IF(Complete@row = 1, "Green")))

Sam Marritz

I figured it out! Here's what I settled on:

=IF(Complete@row < 0.3, "Red", IF(Complete@row < 1, "Yellow", IF(Complete@row = 1, "Green")))

Sam Marritz

Thanks again for your help!

Andrée Starå

I'm always happy to help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.