Conditional Formatting Help

[mjones]

Hi,

I'm using a conditional format 'if cell value' 'is equal to' 0, make
it red. It doesn't work. This is in the cell:

{=SUM(IF($E$6:$E$352=B399,($C$6:$C$352)))}

Any idea why? It turns red if it's not zero. The cell format is _(_
($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_($* 0.00_)_);_(_(@_)_)

I'm having the same problem when I try to make a cell value red when
it's value does not equal the same value in another cell.

Any help would be appreciated.

Thanks,

Michele

 

[Pete_UK]

Perhaps the total in the cell is not exactly zero. If it is a very
small number, however, then it might display as zero even though the
real value might be something like 0.00000043

You can put ROUND(formula,0) around what you already have to ensure
that only integer values are returned from the formula, and if you do
then you will need to commit the amended formula using Ctrl-Shift-
Enter, as it is an array formula.

However, it does not need to be an array formula - you can replace it
with this (entered normally with just <enter>):

=ROUND(SUMIF($E$6:$E$352,B399,$C$6:$C$352),0)

Change the 0 at the end to the number of decimal places that you want
the answer to be evaluated to.

Hope this helps.

Pete

 

[mjones]

Perhaps the total in the cell is not exactly zero. If it is a very
small number, however, then it might display as zero even though the
real value might be something like 0.00000043

You can put ROUND(formula,0) around what you already have to ensure
that only integer values are returned from the formula, and if you do
then you will need to commit the amended formula using Ctrl-Shift-
Enter, as it is an array formula.

However, it does not need to be an array formula - you can replace it
with this (entered normally with just <enter>):

=ROUND(SUMIF($E$6:$E$352,B399,$C$6:$C$352),0)

Change the 0 at the end to the number of decimal places that you want
the answer to be evaluated to.

Hope this helps.

Pete

Thanks. That worked great! I suspected that the number was not
really zero.

 

[Pete_UK]

You're welcome, Michele - thanks for feeding back.

Pete