# Creating RYG based on differences between two percentage columns

Options
✭✭✭

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!

• ✭✭✭✭✭✭
Options

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”.

• ✭✭✭
Options

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,

• ✭✭✭✭✭✭
Options

Do you mean End Date is in the past?

• ✭✭✭
Options

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,

• ✭✭✭✭✭✭
Options

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")))

• ✭✭✭
Options

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")))