How do I use IF formula to change a color based on a percentage column?
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.
Answers
-
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"))
-
I seem to be getting a #Unparseable error message when I applied this formula. Am I missing something?
-
Can you provide a screenshot of the formula open in the sheet as if you are about to edit it?
-
It works.
I checked the formula again and noticed where I had an error, but now I want it to have it reflected as the corresponding colored symbol instead of writing out the color, is that possible? How can I have it reflect as the "red symbol in the GP Health column instead of "red" which is the column the formula you provided me with is in.
Thanks again.
-
You would need to change the column properties to a symbol type column and select the RYG symbol type.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 147 Industry Talk
- 487 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!