# Formula for conditional formatting

1. ## Formula for conditional formatting

Hello,

I'm trying to use conditional formatting to see if the sum of 2 different cells equals the value in a third. In basic terms, I want the excel formula for 'If(L6+M6=I6), then turn the cell green'.

Thanks!  Register To Reply

2. ## Re: Formula for conditional formatting

Here you go:
cond_format.PNG  Register To Reply

3. ## Re: Formula for conditional formatting

- Conditional Formatting > New Rule > Use a Formula to Determine which cells to format > Format values where this formula is true

=And(\$L6+\$M6=\$I6)  Register To Reply

4. ## Re: Formula for conditional formatting

Ah, Thank you for the reply. While I can get it to work on some, it does not work for all for some reason, see pic.

Check.png

In this situation, Sunday + OT = Hours, then turn far right column cell green. It is only working if there are values in both Sunday and OT. If it matters, both Sunday and OT are manually entered while Hours is derived from a formula.  Register To Reply

5. ## Re: Formula for conditional formatting

Here's a sanitized version of the worksheet  Register To Reply

6. ## Re: Formula for conditional formatting

The conditional formatting formula is working; however, I did notice that the condition does not work when the hours results equals to 4.0 or 5.0.
When I manually changed the enclosed formula to 4.0 or 5.0, there was no issue with the CF.

further investigation required.  Register To Reply

7. ## Re: Formula for conditional formatting

Syrkrasi, I haven't looked at the formula or the file but possibly floating point error?  Register To Reply

8. ## Re: Formula for conditional formatting Originally Posted by 63falcondude ... possibly floating point error?
63falcondude, you're correct. jehster1 after looking at the VALUE of the hours, 4.0 was actually 4.00000000000001 so the result would be false.

You could fix this by adding ROUND to the formula in hours.  Register To Reply

9. ## Re: Formula for conditional formatting

Thanks for the input everyone! I set the WB for 'Set Precision as Displayed' and it appears to be working. Will that cause issues down the road? I'm not dealing with numbers more complex than than Hour differences and Currency.  Register To Reply

10. ## Re: Formula for conditional formatting

You're welcome. if that takes care of your initial request, please mark this request as solved.  Register To Reply

11. ## Re: Formula for conditional formatting

To answer question #9, yes it could. I would recommend not setting precision as displayed but rather using the ROUND function to round to a set # of decimal places as mentioned in post #8.

Thanks for the rep!  Register To Reply