+ Reply to Thread
Results 1 to 13 of 13

Using if and And together in conditional formatting multiple rules!

  1. #1
    Registered User
    Join Date
    02-03-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    6

    Using if and And together in conditional formatting multiple rules!

    Hi

    I am not particularlly great with complex formula but I'll give it go!
    I have three rules I am trying to get into conditional formatting for my spreadhseet which are:
    1. If(b29<10% of b24) = equal fill box red
    2. If(b29>=10% of b24)and(b29+c29< b24) =equals fill box red
    3. If(b29>=10% of b24)and(b29+c29>=b24) =equals fill box green

    I am hoping someone can help me!!

  2. #2
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Using if and And together in conditional formatting multiple rules!

    Hello
    For the first two rules as you need Red color so you can combine both the rules
    so the formula would be
    =OR(B29<(0.1*B24),AND(B29>=(0.1*B24),(B29+C29)< B24))

    for the third rule use this one
    =AND(B29>=(0.1*B24),(B29+C29)>=B24)

    Tell me if it works right….

    Hope it helps
    Do ask for any other query you may have…

    If you are satisfied then mark then PLEASE mark this thread as “SOLVED” (by going to thread tools at the top and clicking on “mark this thread as solved”) and you can just click on ADD REPUTATION below my post to say thanks...
    Regards
    Sourabh Gupta

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,439

    Re: Using if and And together in conditional formatting multiple rules!

    May be:

    =B29<B24*0.1

    =AND(B29>=B24*0.1,B29+C29<B24)

    =AND(B29>=B24*0.1,B29+C29>=B24)

  4. #4
    Registered User
    Join Date
    02-03-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    6

    Re: Using if and And together in conditional formatting multiple rules!

    Thank you, it is accepting the formula (which I couldn't get it do) but its not applying the formatting!

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,439

    Re: Using if and And together in conditional formatting multiple rules!

    Mine and Sourabh's returns TRUE/FALSE and can be accepted to be CF criteria!

    If it doe not work, try topost a small dummy workbook.

  6. #6
    Forum Expert sourabhg98's Avatar
    Join Date
    10-22-2014
    Location
    New Delhi, India
    MS-Off Ver
    Excel 2007, 2013
    Posts
    1,899

    Re: Using if and And together in conditional formatting multiple rules!

    Remember while doing conditional formatting that you select the correct range....and maybe try these formula
    =OR($B$29<(0.1*$B$24),AND($B$29>=(0.1*$B$24),($B$29+$C$29)< $B$24))
    and
    =AND($B$29>=(0.1*$B$24),($B$29+$C$29)>=$B$24)
    this would fix the criteria cells ..B24 and B29 would be used as a criteria for all cells now....

    Tell me if it works right….

    Hope it helps
    Do ask for any other query you may have…

    If you are satisfied then mark then PLEASE mark this thread as “SOLVED” (by going to thread tools at the top and clicking on “mark this thread as solved”) and you can just click on ADD REPUTATION below my post to say thanks...
    Regards
    Sourabh Gupta

  7. #7
    Registered User
    Join Date
    02-03-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    6

    Re: Using if and And together in conditional formatting multiple rules!

    Thanks guys like i said don't have the best skills!!! ok so heres a dummy version of my spread sheet
    dummy.xls

    Hope thats attached ok!

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Using if and And together in conditional formatting multiple rules!

    Seems to me that you only need one CF. Statically format the cell as red, then

    =(b29 >= 0.1 * b24) * (b29+c29 >= b24)

    ... and format as green.
    Entia non sunt multiplicanda sine necessitate

  9. #9
    Registered User
    Join Date
    02-03-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    6

    Re: Using if and And together in conditional formatting multiple rules!

    The cell that I am looking at has a formula in it iteslf, would that effect the conditional formatting?

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Using if and And together in conditional formatting multiple rules!

    Cells with CF usually do.

  11. #11
    Registered User
    Join Date
    02-03-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    6

    Re: Using if and And together in conditional formatting multiple rules!

    thats what i thought, the conditional formula works if i do it on the cell below it works perfect!!!!

  12. #12
    Registered User
    Join Date
    02-03-2015
    Location
    London
    MS-Off Ver
    2010
    Posts
    6

    Re: Using if and And together in conditional formatting multiple rules!

    Quote Originally Posted by shg View Post
    Seems to me that you only need one CF. Statically format the cell as red, then

    =(b29 >= 0.1 * b24) * (b29+c29 >= b24)

    ... and format as green.
    Thats done it! Thanks you so much

  13. #13
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Using if and And together in conditional formatting multiple rules!

    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. Conditional Formatting, multiple rules
    By jsch08 in forum Excel General
    Replies: 1
    Last Post: 04-30-2013, 11:30 PM
  2. Conditional Formatting with multiple rules
    By carrod65 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-24-2012, 03:43 PM
  3. Conditional Formatting with multiple rules
    By Peter Richardson in forum Excel General
    Replies: 6
    Last Post: 07-07-2011, 02:08 AM
  4. Conditional formatting multiple rules
    By candrew03 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-16-2011, 02:35 PM
  5. Conditional Formatting Multiple Rules
    By ems.payroll in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-07-2008, 12:31 PM

Tags for this Thread

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