Use of ABS in Excel 2010 conditional formatting

[Wolfy1s13]

I have columns that contain both positive and negative values and wish to colour code the absolute values e.g

0-9 Green
10-19 Yellow
20-29 Amber
>30 Red

I have set up conditional formatting for the column range as shown below

=ABS($N45)<10 for Green
=IF($N45<>"",ABS($N45)>=10,"") for yellow

but this shows 10 as green and -10 as yellow. The cells in the column are formatted as 'Numbers' with no decimal points.

Has anyone come across this problem and resolved it ---- please advise

 

[Norie]

When I enter manually -10 or 10 then I get yellow for both.

Where is the value in N45 coming from?

 

[Wolfy1s13]

When I enter manually -10 or 10 then I get yellow for both.

Where is the value in N45 coming from?

$N45 in the conditional formatting is a calculated field of the form

=IF($M15<>"",($M15-$L$39)*1440,"") gives a value of 10
=IF($M16<>"",($M16-$L$39)*1440,"") gives a value of -10

where $M15/6 and $L$39 are a custom fields of the form hh:mm.

I think the problem is in the definition of the $N45 field but I cannot see why

 

[Norie]

I don't see a problem with the 'definition' of $N45 but I can see potential for rounding errors, especially when you appear to be dealing with time.

If you increasing the no of decimal places in the formatting N45 what do you see?

 

[Wolfy1s13]

I don't see a problem with the 'definition' of $N45 but I can see potential for rounding errors, especially when you appear to be dealing with time.

If you increasing the no of decimal places in the formatting N45 what do you see?

Updated $N45 to two decimal places but it has no effect

If I use two completely new cells one with 10 and the other one with -10 and apply the same conditional formatting both are yellow. If I populate those cells using the N45 calculation , ABS(Cell) <9 and ABS(cell)>=10, the positive value is green and the negative with yellow.

 

[Wolfy1s13]

Updated $N45 to two decimal places but it has no effect

If I use two completely new cells one with 10 and the other one with -10 and apply the same conditional formatting both are yellow. If I populate those cells using the N45 calculation , ABS(Cell) <9 and ABS(cell)>=10, the positive value is green and the negative with yellow.

Sorry the calculation is e.g. =IF($M16<>"",($M16-$L$39)*1440,"") not as in previous line

 

[Norie]

When dealing with time the rounding errors can be very small.

What happens if you add ROUND to the formulas?

 

[Wolfy1s13]

When dealing with time the rounding errors can be very small.

What happens if you add ROUND to the formulas?

Tried ROUND which worked - thanks.
However also tried a change to the conditional formatting

Changed =ABS($N45)<10 to ABS($N45) <= 9​

and

=IF($N45<>"",ABS($N45)>=10,"") to

=IF($N45<>"",ABS($N45)>9,"")

and that worked as well?

Thanks for your time and advice. Really appreciated as it was driving me mad.
​

​

<strike></strike>