CondForm not applying to zero, "0", when it should.

[MitchellWMA]

I've just searched through all my posts in this ng. I'm almost 99%
positive that I've run into this "zero" problem before.

I've set up conditional formatting in a column so that if the cell
value is "less than or equal to" and then put "0" in the box, that
certain formatting will be applied to those cells.

But what I've discovered is that in testing the conditional
formatting, some obey this formatting with a zero and some don't. I
seem to remember something about zeros being able to be positive or
negative and that we had to do something to change it so that the
zeroes would all be formatted properly.

Since I now carry around all my recent workbooks on my USB flash
drive, I might even still be carrying around that workbook except I
can't remember what it was that was having what believe is an
identical problem.

Appreciate any help. And THIS TIME, the answer is going into my Tips
folder where I've been keeping such information on my 4GB memory
stick.

Thanks! D

 

[Harlan Grove]

I've set up conditional formatting in a column so that if the cell
value is "less than or equal to" and then put "0" in the box, that
certain formatting will be applied to those cells.

....

Really "0" including the double quotes? If so, Excel would use a TEXT
comparison, and ALL numbers are LESS THAN ANY text, e.g., as far as
Excel is concerned the formula =1E+300<"0" is TRUE. And for text
comparisons, ="-1E+300"<"0" is FALSE. Do you really want TEXT
comparisons?

If you want numeric comparisons, enter 0 rather than "0" as the value
in the conditional formatting condition.

 

[T. Valko]

Are the cells in question the results of formulas?

It may be that the result is not exactly 0 but is being displayed as 0:

0.00000002 but displays as 0.0

If this is the case then you should use rounding in those formulas and round
to the significance that you need.

 

[Gord Dibben]

Zeros cannot be positive or negative, but what looks like a zero in a cell may
be just a formatting issue.

i.e. .000012 formatted to 1 decimal place will show a zero in the formatted
cell.

CF knows it is not zero.

You can get around this if you have no other option by Tools>Options>Calculation

Checkmark Precision as displayed

Note this is a workbook setting and will permanently change all numbers in all
sheets to what is displayed.


Gord Dibben MS Excel MVP

 

[Harlan Grove]

Zeros cannot be positive or negative, . . .

Mathematically true, but IEEE floating point values can have just the
sign bit 1 and all other bits 0, which is -0. And while they're rare
in Excel, they can be produced.

=QUOTIENT(1,-2)

=QUOTIENT(1,-2)<0