+ Reply to Thread
Results 1 to 11 of 11

Formula for conditional formatting

  1. #1
    Registered User
    Join Date
    05-21-2018
    Location
    DC
    MS-Off Ver
    2016
    Posts
    41

    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. #2
    Valued Forum Contributor than_gold's Avatar
    Join Date
    10-17-2017
    Location
    Coimbatore India
    MS-Off Ver
    Office 365
    Posts
    646

    Re: Formula for conditional formatting

    Here you go:
    cond_format.PNG
    Regards,
    Thangavel D

    Appreciate the help? CLICK *

  3. #3
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    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)
    If your original question was resolved, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
    Click on the Add Reputation button (located at the lower-left corner of all post) for those who assisted you in solving your issue.

  4. #4
    Registered User
    Join Date
    05-21-2018
    Location
    DC
    MS-Off Ver
    2016
    Posts
    41

    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. #5
    Registered User
    Join Date
    05-21-2018
    Location
    DC
    MS-Off Ver
    2016
    Posts
    41

    Re: Formula for conditional formatting

    Here's a sanitized version of the worksheet
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    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. #7
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Formula for conditional formatting

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

  8. #8
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: Formula for conditional formatting

    Quote Originally Posted by 63falcondude View Post
    ... 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. #9
    Registered User
    Join Date
    05-21-2018
    Location
    DC
    MS-Off Ver
    2016
    Posts
    41

    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. #10
    Valued Forum Contributor
    Join Date
    12-01-2016
    Location
    Planet Earth
    MS-Off Ver
    95 - 2016
    Posts
    343

    Re: Formula for conditional formatting

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

  11. #11
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 06-19-2015, 07:16 AM
  2. Formatting Cells with Date or Text Values in a Conditional Formatting Formula
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 09:36 AM
  3. Opening xlsm files with conditional formatting opens with removed conditional formatting
    By Martijn.Steenbakker in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-07-2014, 05:38 AM
  4. If Formula - conditional formatting - three different formatting rules
    By sharper1989 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-25-2014, 04:49 PM
  5. Replies: 3
    Last Post: 11-28-2013, 02:11 AM
  6. Replies: 1
    Last Post: 07-19-2012, 05:37 AM
  7. [SOLVED] How do I do a complex conditional in a conditional formatting formula
    By Ray Stevens in forum Excel General
    Replies: 6
    Last Post: 03-12-2006, 06:30 PM

Bookmarks

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