# 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!

2. ## Re: Formula for conditional formatting

Here you go:
cond_format.PNG

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)

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.

5. ## Re: Formula for conditional formatting

Here's a sanitized version of the worksheet

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.

7. ## Re: Formula for conditional formatting

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

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.

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.

10. ## Re: Formula for conditional formatting

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

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!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1