Some cells in Excel rounding correctly to an integer but not applying conditional formatting

Question

I have a column in Excel 2010 which is formatted as a number with decimals set to 0. It also has conditional formatting to color the cells green, yellow or red based on their value range. It all works fine except for certain cells whose values(before being rounded to nearest integer) are decimals numbers near the threshold or the conditional formatting. For example, the range for yellow formatting in column D(Dia) is 80-89 and you enter 80.1. The decimals are removed and rounded correctly to 80 but there is no color formatting. However, if you enter 81.1 or 79.9, the rounding and color formatting work correctly.

The conditional formatting rules are 60-80 = green background, 81-89 = Yellow and 90-200 and 1-59-red.

This is not a big issue for this table as the numbers are entered manually as whole numbers. But when they are averaged by day in my pivot table which uses this table as a reference, and has identical conditional formatting, those cells are not formatted.

Any cells that are not green, yellow or red are missing the formatting.

Please help. My blood pressure is skyrocketing trying to solve this. Haven't found any useful info by googling.

Answer 1

As the value for the conditional formatting is the underlying value regardless number format, there is a gap between 80 and 81 not being considered by the conditional.

The solutions for this problem could be

1. overlap the values in the conditional format for example from 60-80 , 80 - 100, et c.

or

2. make the limits decimals numbers with higher precision than the input value, e.g. from 60 - 80.9999999, 81 to 100.9999999 et c.