conditional formatting cell that are not zero (with exception)

[TheUnconquered]

Hello,

I have a conditional format set up right now that works most of the time for my purpose, but not all the time. How it's set up now, is if the cell is less than or more than zero it formats the cell red. The problem I have is sometimes there is very small numbers showing up in the output of the formula (like 0.00000001, for example). I don't need it to match that perfectly to zero. If it's off by a $1, fine, it can show as red. If it's off by less than a $1, I don't need to know that. How can I set this up in conditional formatting?

 

[dcardno]

Test for a value of abs(X) < 0.01, where X is the cell you are testing and the value 0.01 is sufficiently small that you consider it to be zero.

 

[TheUnconquered]

I'm not following... I was thinking there would be a formula I could use within the conditional formatting rules that would make this work, but I don't know how to set that formula up.

 

[johnnyL]

Use two conditional formulas for the cell:

one for Checking less than a value that you deem acceptable ie < -.99999999

then set another one for the cell to check greater than a value that you deem acceptable ie. > .99999999

 

[TheUnconquered]

Use two conditional formulas for the cell:

one for Checking less than a value that you deem acceptable ie < -.99999999

then set another one for the cell to check greater than a value that you deem acceptable ie. > .99999999

Yes, of course. Easy solution. I should've thought of this. Guess I've been staring at this spreadsheet for too long today. ?‍ Thank you!

 

[johnnyL]

Your welcome @blakemiller

 

[Peter_SSs]

Whilst that will work, you can do it with a single CF condition, along the lines suggested by @dcardno

21 08 02.xlsm
	B
1	Value
2	0.0000001
3	0.5
4	0.00000001
5	25000
6	-35
7	-0.000000001
CF Away from Zero

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B7	Expression	=ABS(B2)>0.000001	text	NO

 

[TheUnconquered]

Whilst that will work, you can do it with a single CF condition, along the lines suggested by @dcardno

21 08 02.xlsm
	B
1	Value
2	0.0000001
3	0.5
4	0.00000001
5	25000
6	-35
7	-0.000000001
CF Away from Zero

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2:B7	Expression	=ABS(B2)>0.000001	text	NO

Ok, I see what he was referring to now. This makes sense also. A way to do it with a single CF formula. Great! Thank you.

 

[Peter_SSs]

Ok, I see what he was referring to now. This makes sense also. A way to do it with a single CF formula. Great! Thank you.

Cheers.