How do I use IF formula to change a color based on a percentage column?

Tanya216

I could greatly use some assistance with creating a formula for my sheet that would capture the following:

Currently I have a sheet that contains the following data points:

Quoted GP | Quoted Margin % | Actual GP | Actual GP % | GP Health

$7857 35% $8970 24% ??

In this sheet, I want to be able to do the following, I have set up the GP Health Column to reflect status symbols (Green, Yellow, Red). I need to create a formula that would allow the following:

• If the "Actual GP %" is +/- 5% of the Quoted Margin % mark the GP Health as "Green"
• If the "Actual GP %" is +/- 5-15% of the Quoted Margin % mark the GP Health as "Yellow"
• If the "Actual GP %" is +/- >15% of the Quoted Margin % mark the GP Health as "Red"

Thank You.

Paul Newcome

Try something like this:

=IF(ABS([Quoted Margin %]@row - [Actual GP %]@row) > 0.15, "Red", IF(ABS([Quoted Margin %]@row - [Actual GP %]@row) <= 0.05, "Green", "Yellow"))