Conditional formatting issue

[AFIOF719]

Hello-

I am having an issue where I have a conditionally formatted cells where is the value = 0, then to highlight green, if not, then highlight red. Even though a bunch of the cells are returning a value of 0, it is being highlight red. Please advise.

The cells in question are in the first tab named "VALIDATE." Cells C7, C9, and C13 are returning correct formatting, but cells C6,C8,C10,C11,C12,C14 and C15 are not.

Any help would be greatly appreciated.

[TMS]

Select the cells and increase the number of visible decimal places (to about 15). You'll see you have some very small negative numbers from the calculations.


Regards, TMS

[AFIOF719]

TMS-

Thank you, you are bringing me closer to the solution. Can you tell why these small negative numbers are happening? I increased the visible decimal places on all the cells in the workbook, and none, besides in the validate tab are showing very small negative numbers.

Thanks,

[FDibbins]

Trevor, it's being caused by that binary error.

Look at C6...
c6=IF($B6=$A6,'Actual & Forecast'!B$3-Deposits!B$5,"")
=-0.00000000000023

but...
='Actual & Forecast'!B$3-Deposits!B$5
=0.000000000000000000000000
'Actual & Forecast'!B$3=1,452.350000000000000
Deposits!B$5=1,452.350000000000000

Freaky

Can be solved using ...
=IF($B6=$A6,ROUND('Actual & Forecast'!B$3-Deposits!B$5,2),"")

[AFIOF719]

FDibbins, thanks! That solved the issue, but yeah, still kind of freaky.

Thanks again.

[TMS]

That, as you say, is extremely weird.

As soon as you bring in the IF comparison, and it doesn't seem to matter how that is done, you get the issue.

Played about for a while but I can't see a way around other than using ROUND.

Regards, TMS