+ Reply to Thread
Results 1 to 9 of 9

Conditional formating with equal or more with problems !!

  1. #1
    Forum Contributor
    Join Date
    12-17-2019
    Location
    Portugal
    MS-Off Ver
    office 2019
    Posts
    167

    Conditional formating with equal or more with problems !!

    HI,

    In the sheet what I would like to know is how to put a conditional format that appears in "green" (equal or higher) or "light brown" (if is lower).

    It will be equal for all , but I will explain you for 1 row:

    The conditional is in "D6" ---- if "D6" =< "Q7" (green) if is > (light brown)
    "G6" ---- if "G6+D6" =< "Q7" (green) if is > (light brown)
    "J6" ---- if "G6+D6+J6" =< "Q7" (green) if is > (light brown)
    "M6" --- if "M6+G6+D6+J6" =< "Q7" (green) if is > (light brown)
    "P6" --- if "P6+M6+G6+D6+J6" =< "Q7" (green) if is > (light brown)

    I thing the problem is when is equal !!! (see cell "J71") must be green but appears light brown !!
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Conditional formating with equal or more with problems !!

    I think you didnt include column "M" in any of your conditions....

  3. #3
    Forum Contributor
    Join Date
    12-17-2019
    Location
    Portugal
    MS-Off Ver
    office 2019
    Posts
    167

    Re: Conditional formating with equal or more with problems !!

    didnt include column "M" and "P" in any of my conditions, after I see that as a problem with the formula

  4. #4
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Conditional formating with equal or more with problems !!

    So - you resolved the problem?

  5. #5
    Forum Contributor
    Join Date
    12-17-2019
    Location
    Portugal
    MS-Off Ver
    office 2019
    Posts
    167

    Re: Conditional formating with equal or more with problems !!

    unfortunately I didn't resolve the problem.
    Let me put again the sheet and tell you that I don't understand why the cells ("M16" - "M31" - "M46" and "M111" are in light brown (must be green!) because the result is EQUAL !!!
    The same problem with "J71" !!!
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Conditional formating with equal or more with problems !!

    In fact - if you look at the decimals
    the total of your cells in line 16 would be 2,349.73010 € vs 2,349.7300 in Q17
    so the total is indeed > Q17 and that's why the color brown.

    What you need to do is add to your conditions the ROUND function, so that it will ignore decimals like this:

    =ROUND($D11,0)+ROUND($G11,0)+ROUND($J11,0)+ROUND($M11,0)>ROUND($Q12,0)


    =+ROUND($D11,0)+ROUND($G11,0)+ROUND($J11,0)+ROUND($M11,0)<=ROUND($Q12,0)

  7. #7
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Conditional formating with equal or more with problems !!

    Or just take 2 decimals , and change the "0" in the formula to "2"

    =ROUND($D11,2)+ROUND($G11,2)+ROUND($J11,2)+ROUND($M11,2)>ROUND($Q12,2)

  8. #8
    Forum Contributor
    Join Date
    12-17-2019
    Location
    Portugal
    MS-Off Ver
    office 2019
    Posts
    167

    Re: Conditional formating with equal or more with problems !!

    fantastic !!!!!!! Many thanks !!!

  9. #9
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: Conditional formating with equal or more with problems !!

    You're welcome.

+ 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. Problems with conditional formating
    By robert26 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-17-2018, 06:58 AM
  2. [SOLVED] Conditional Formating when row does not equal or contain reference cell's value
    By mariomp in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-28-2016, 03:16 AM
  3. Replies: 5
    Last Post: 03-14-2014, 04:03 AM
  4. Replies: 4
    Last Post: 01-23-2014, 10:14 AM
  5. Replies: 6
    Last Post: 08-14-2006, 05:00 PM
  6. Replies: 2
    Last Post: 03-27-2006, 12:10 PM
  7. Conditional formating-change colour when D30 is equal
    By smith.james0 in forum Excel General
    Replies: 2
    Last Post: 07-26-2005, 01:05 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