Conditional Formatting with a formula, highlighting errors

0
votes

I have a column (I) that is a sum of total hours worked for a month. I'm trying to apply a conditional formatting rule that will highlight cells that equal 0 but aren't blank.

The formula I'm using is: 

AND(I2=0,NOT(ISBLANK(I2)))

It highlights most of the 0 values (but not all of them) and it is also highlighting some non-zero values.

I don't know if will help, but this is a sample of what I'm getting:

excel example

3
excelconditional-formatting
How did you apply this? I know Cond. Formatting can be tricky when applying over a column. I would go to I2, apply that formula (note that you'll need = before that), then apply. Then change the range from =$I$2 to =I:I. See if that helps. – BruceWayne
That solved it. Thanks. – Laurel

3 Answers

0
votes

I had a simmilar problem a few months ago. Mark Fitzgerald pointed out the right direction to me. Times in Excel are all percentages of days. Computers calculate in binary so all those decimals have to be converted to binary before the math operation and the result has to be converted back to decimal. But decimal places are limited to 15 in Ms Excel. You can check the whole answer here.

0
votes

I think your issue is the Applies to range (though I am surprised that the Total label is not then coloured blue). It seems to me the formatting is correct but offset by one row. For simplicity, I suggest selecting the entire Total column and applying: 

=AND(I1=0,NOT(ISBLANK(I1)))

Or select the range to format starting in the first cell below Total (ie Row2) and apply your formula.

Taking the 'simpler' approach, if you don't want the label formatted you might give it a rule of its own, with No Format Set chosen and make sure it has priority (eg at the top of the list with Stop If True checked).

@Dimitrios has a point (but it seems also a solution) if the values are being calculated.

-1
votes
=AND(I2=0,NOT(ISBLANK(I2)))

Enter the conditional formatting as a formula into the first cell. Click 'Ok'. Then copy the first cell and paste special as "formatting" to the remaining cells.

This will work as you desire.