+ Reply to Thread
Results 1 to 2 of 2

Conditional Formatting - Custom Formula help needed

  1. #1
    Registered User
    Join Date
    07-26-2014
    Location
    Nottingham, UK
    MS-Off Ver
    Microsoft Office Professional 2010
    Posts
    32

    Conditional Formatting - Custom Formula help needed

    Hi,
    I'm using Google Sheets but think it works the same way as it does in Excel.

    Regarding conditional formatting, are there any easier ways of writing custom formulas to highlight a cell that's based on multiple other cells that all have the same condition?

    e.g. The range to highlight is A1:C1

    Is there a better custom formula that the ones I've written for the following example scenarios...

    SCENARO 1
    IF - E1,F1,G1,H1,K1,L1,M1 are all true

    custom formula: =and($E1=true,$F1=true,$G1=true,$H1=true,$K1=true,$L1-true,$M1=true)

    SCENARO 2
    IF D1,E1 & F1 are ALL false but EITHER G1, H1 or K1 are true

    custom formula: =and($D1=false,$E1=false,$F1=false,or($G1=true,$H1=true,$K1=true))

    SCENARO 3
    EITHER B1 OR C1 is true AND any of E1,G1, K1 are true)

    custom formula: =and(or($B1=true,$C1=true),or($E1=,$G1=true,$K1=true))

    Please tell me there's a better, easier way of writing these formulas because I've got a scenario where I need each row of cells (within the ranges A1:C35,E1:Y35) to highlight if EITHER B or C is true (for that particular row) and ALL of the cells between E and Y are false - I don't want to have to list every single cell in the formula but I don't know if there's any other way to do it.

    I tried =and(or($B1=true,$C1=true),$E1:$Y1=false) but all that does is highlight the E to Y cells that match those conditions, when I need it to ALSO highlight the A to C cells.

    PLEASE HELP!
    Sally

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,943

    Re: Conditional Formatting - Custom Formula help needed

    If you have True and False as your cell values, there is no need to compare to TRUE

    For example, you an change

    =and($E1=true,$F1=true,$G1=true,$H1=true,$K1=true,$L1-true,$M1=true)

    to

    =and($E1,$F1,$G1,$H1,$K1,$L1,$M1)

    But for false:

    =and($D1=false,$E1=false,$F1=false,or($G1=true,$H1=true,$K1=true))

    =and($D1=false,$E1=false,$F1=false,or($G1,$H1,$K1))



    And change

    =and(or($B1=true,$C1=true),$E1:$Y1=false)

    to


    =and(or($B1=true,$C1=true),COUNTIF($E1:$Y1,false)=21) (change the 21 to the max number of falses in E:Y)

    and you can change

    =and(or($B1=true,$C1=true),or($E1=,$G1=true,$K1=true))

    to just

    =and(or($B1,$C1),or($E1,$G1,$K1))
    Last edited by Bernie Deitrick; 11-16-2022 at 05:05 PM.
    Bernie Deitrick
    Excel MVP 2000-2010

+ 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 Custom Formula
    By mrbarba in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-24-2019, 01:02 AM
  2. Conditional Formatting Formula Needed
    By jeff17408 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 03-08-2017, 02:42 PM
  3. [SOLVED] Help with a Custom Formula Conditional Formatting Rule
    By rtommie84 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-10-2016, 09:17 PM
  4. [SOLVED] Conditional Formatting: Custom Formula
    By sschroe954 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-02-2016, 05:34 PM
  5. Formula needed for conditional formatting
    By NEHicks in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-22-2013, 11:28 AM
  6. Conditional Formatting Formula Needed
    By AJT82 in forum Excel General
    Replies: 2
    Last Post: 10-08-2011, 05:15 AM
  7. Conditional formatting formula help needed
    By gloworm in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-15-2011, 03:12 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