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

Options

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.

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!