Thread: Plus/Minus Function

Hello, I'm trying to create a formula where I can compare two cells but accept them as equal is one is plus/minus 0.1.
Example:
Cell A1 is 3, Cell A2 is 3.2, I want Cell A3 to turn red (or report "1")
Cell A1 is 3, Cell A2 is 3.1, I want Cell A3 to turn green (or report "0")

I'm hoping this is an easy one.

Thanks,
Tim

Originally Posted by TimmyK

Hello, I'm trying to create a formula where I can compare two cells but accept them as equal is one is plus/minus 0.1.
Example:
Cell A1 is 3, Cell A2 is 3.2, I want Cell A3 to turn red (or report "1")
Cell A1 is 3, Cell A2 is 3.1, I want Cell A3 to turn green (or report "0")

I'm hoping this is an easy one.

Thanks,
Tim

=--not(AND((a1+0.1)>=a2,(a1-0.1)<=a2))

or in conditional formatting

formula is
=AND((a1+0.1)>=a2,(a1-0.1)<=a2) for green

=not(AND((a1+0.1)>=a2,(a1-0.1)<=a2)) for red

Select A3 and go to Format|Conditional Formatting

Select Formula Is and enter formula: =ABS(A2-A1)<=0.1

Click Format and pick green from pattern tab

Click Ok.

Click Add

Select Formula Is and enter formula: =ABS(A2-A1)>0.1

Click Format and pick red from pattern tab

Click Ok