IF(AND Formula + substraction

Indira Buike

Hello!

I have the % Burn Rate of my project and the % Complete, I want to subtract and according to the difference in them I want Harvey Balls to display different colors.

If the difference between the % Burn Rate and the % Complete is => 6% I want it to be "Red"

If the difference is From 1% to 5% I want to be "Yellow"

And if the difference is <= 0% I want it to be "Green"

This is my formula:

=IF(AND([Task Name]$8 - [% Complete]$10 = 0.01, [Task Name]$8 - [% Complete]$10 = 0.05, "Yellow", IF([Task Name]$8 - [% Complete]$10 >= 0.06, "Red", "Green")))

[Task Name]8 is my Burn Rate % is cell linked to another sheet.

[% Complete] is the percentage complete of the project that is calculated from smartsheet itself.

The first part of the formula doesn't work and I'm not sure why, please help :(

Sravya Gupta

Hi, try this:

=IF(([Task Name]$8 - [% Complete]$10) >= 0.06, "Red", IF([Task Name]$8 - [% Complete]$10 <= 0, "Green", "Yellow"))

Sravya Gupta

Hi, try this:

=IF(([Task Name]$8 - [% Complete]$10) >= 0.06, "Red", IF([Task Name]$8 - [% Complete]$10 <= 0, "Green", "Yellow"))

Indira Buike

Thank you @Sravya Gupta it worked!