IF(AND Formula + substraction


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 :(


Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!