Forum Discussion
Lollipop23
May 24, 2023Copper Contributor
Show signal using conditional formatting by formula
Hi!
I have question regarding the conditional formatting. How to show signal using conditional formatting icon set?
I'm planning to make a signal when the 3 or 5 value is showing nearest value among them (example in the picture), the signal will show red flag in C11. The thing is the value is not fixed. So, how can I set the conditional formatting?
Thank you in advance.
- mtarlerSilver ContributorI'm sorry I totally don't follow what the comment "when the 3 or 5 value is showing nearest value among them" means. That said, conditional formatting has an option 'based on custom formula'. When you enter that formula you make it based on the upper left cell in the 'Applied To' range and everything is relative to that upper left cell unless marked with a '$'. So in the image if you Applied to range is C2:C100 then make the conditional formula based on cell C2. So =MAX(ABS($B2 - $B3:$B6)) < 5 would check if the next 4 values are all <5 away from this value. Notice how I have a '$' in front of the 'B' but not the numbers. This means column B is 'locked' but the rows are not and will be relative to the row the conditional formatting is checking. In this case the column isn't changing so that '$' isn't needed except as a talking point here. Hope that gives you an idea of how to do what you need.