Conditional Formatting_Urgent

[taruna1986]

Hi,

I am trying to change the color of a cell where variance is zero through conditional formatting. But since there are decimal values after zero, the system is not letting me apply conditional formatting.

What formula should I put in so that conditional formatting ignores decimals after zero?

Appreciate if somebody could help.

Thanks
Taruna

 

[DRSteele]

Try function TRUNC.

 

[jtakw]

Hi,

Try:

=INT(A1)=0

 

[fhqwgads]

=AND(A1<1,A1>-1)

 

[taruna1986]

Thanks to both of you for a quick reply. Is it also possible to apply Else condition with the formula =AND(A1<1,A1>-1) in conditional formatting? I mean if it is not zero then change the color to red.

Attaching below the snip of my work.

 

[jtakw]

Hi,

Try:

=INT(A1)=0

You'll need 2 separate CF for 2 different colors:

=INT(A1)=0
for 0 values (ignoring fractions)

=INT(A1)<>0
for values Not equal 0

 

[taruna1986]

Thanks to all of you for a quick reply. Is it also possible to apply Else condition with the formula =AND(A1<1,A1>-1) in conditional formatting? I mean if it is not zero then change the color to red.

Attaching below the snip of my work.

View attachment 57916

@fhqwgads@jtakw@DRSteele

 

[jtakw]

You'll need 2 separate CF for 2 different colors:

=INT(A1)=0
for 0 values (ignoring fractions)

=INT(A1)<>0
for values Not equal 0

If you may have negative values, you can use these:

=INT(ABS(A1))=0

=INT(ABS(A1))<>0

 

[taruna1986]

If you may have negative values, you can use these:

=INT(ABS(A1))=0

=INT(ABS(A1))<>0

It is working with adding ABS formula now. Thank you so much.

 

[jtakw]

You're welcome.