# Conditional Formatting - Formula Problem?

1. ## Conditional Formatting - Formula Problem?

I have a spreadsheet with a list of payments made against contracts. Columns A:D display contract_number, contract_value, Payment_date and payment_amount. In column E, there is a formula to sum the payments at each change of contract number: =IF(A2=A3,"",SUM(D\$2:D2)-SUM(E1:E\$2)). No big problems with this.

I have also applied some conditional formatting on column E to highlight when the sum in column E (payments) are not equal to the value in column C (contract_value): =AND(E3<>B3,E3<>"")

So, if the total invoiced against the contract (E3) is different to The contact value (B3) AND there is a value in E3, then apply formatting.

However, this doesn't seem to be working very well as several of the cells in column E have the formatting applied where the conditions are not being met.

Help appreciated.

Ken  Register To Reply

2. ## Re: Conditional Formatting - Formula Problem?

you went into problems with rounding values. (not your fault - just known excel problem).
The numbers in excel are stored as double precision (some 15 digits shall be acurate)

see rows 14 and 15 if you use not 2 decimal digits but more:

HTML Code:
of course your rule for conditional formatting returns red, because value in column B is different from E

The cure (one of possible) - change the CF rule formula to: ``Please Login or Register  to view this content.``  Register To Reply

3. ## Re: Conditional Formatting - Formula Problem?

Kaper,

Thank you very much for your help. I didn't know about the precision level. Who would have thought that adding two numbers together would 'introduce an error'.

Now I am aware of the error, I decided to change the CF to this, which also works:
HTML Code:  Register To Reply