Creating RYG based on differences between two percentage columns

John Kelly

I have a percentage planned and a percentage complete column in a project sheet and want to acheive the below:

Highlight Red ball when % complete is more than 5% below % planned.

Highlight Yellow ball when % complete is between 5 & 1% below % planned.

Highlight Green ball when % complete is >= % planned.

Appreciate any guidance!

Paul Newcome

Try something like this:

=IF(Planned@row ⇐ Actual@row, “Green”, IF(Planned@row ⇐ Actual@row + 0.05, “Yellow”, “Red”))

It seems as though there are some formatting issues here in the community forum at the moment. You are going to need to retype the formula directly in Smartsheet (do not copy/paste from here) to get the proper quotes and replace each ⇐ with an < followed immediately by an = for “less than or equal to”.

John Kelly

Thanks Paul,

That worked for me. I used this formula =IF([% Planned]@row <= [% Complete]@row, "Green", IF([% Planned]@row <= [% Complete]@row + 0.05, "Yellow", "Red"))

Can I add a stipulation that if End date column is greater than today and % planned is not 100% the Red ball is highlighted?

Thanks,

Paul Newcome

Do you mean End Date is in the past?

John Kelly

Hi Paul, I made no sense in that last message.

Yes, if end date is in the past and % complete is less than 100%, the red ball is highlighted.

Thanks,

Paul Newcome

Give this a go...

=IF(AND([End Date]@row< TODAY(), [% Complete]@row <> 1), "Red", IF([% Planned]@row <= [% Complete]@row, "Green", IF([% Planned]@row <= [% Complete]@row + 0.05, "Yellow", "Red")))

John Kelly

Hi Paul,

Got it sorted in the meantime with

=IF(AND([% Complete]@row < [% Planned]@row, Finish@row < TODAY()), "Red", IF([% Planned]@row <= [% Complete]@row, "Green", IF([% Planned]@row <= [% Complete]@row + 0.03, "Yellow", "Red")))

Thanks for your help