+ Reply to Thread
Results 1 to 8 of 8

Formula for conditional formatting

  1. #1
    Registered User
    Join Date
    08-19-2017
    Location
    Hawaii
    MS-Off Ver
    2013
    Posts
    27

    Formula for conditional formatting

    Hi,
    Being new to EXCEL I get confused with formulas, and which ones to use.

    I have a golf scoring spread sheet and was wondering if EXCEL can highlight certain scores if certain conditions were met & how do I do it?

    First of all, the person's class could change because of their scores going either higher or lower.

    Column A has different "Class" of players from A-E
    Row 1 has the par for each hole

    If column A = A, then highlight cells with scores that are 1 above par or greater
    If column A = B, then highlight cells with scores that are 2 above par or greater
    If column A = D, then highlight cells with scores that are 3 above par or greater
    If column A = E, then highlight cells with scores that are twice the value of par or greater

    If column A = C, then highlight cells with scores =>+2 on par 5, with scores =>+3 on par 4 & with scores =>+1
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Formula for conditional formatting

    Is this what you wanted? Formulae are all variants of this:

    =AND($A3="Group A",B3-B$1=1)
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    08-19-2017
    Location
    Hawaii
    MS-Off Ver
    2013
    Posts
    27

    Re: Formula for conditional formatting

    Thank You Glenn.

    I should have written ex. "1 above & greater".

    Using your formula I noticed it didn't have a ">" sign.
    Where would the ">" be inserted?

    Could you please explain Group C formula? =AND($A3="Group C",B3-B$1=B$1-2)
    Do I need to string a combination of formulas to accomplish Group C?
    Last edited by Hydroman72; 10-09-2018 at 01:18 AM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Formula for conditional formatting

    Glenn is testing 2 things, and both must be true - you use AND for that. If Only 1 needs to be true, you could use OR.

    So that formula says A3=Group AND B3-B1=1
    because CF only needs a TRUE or FALSE answer, thats all you need.

    To make it >=1...
    =AND($A3="Group A",B3-B$1>=1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Formula for conditional formatting

    For Group C, is this the rule (a bit was missing from your post):

    Scores =>+2 on par 5,
    Scores =>+3 on par 4
    Scores =>+1on par 3

    The sequence looks odd. Is that REALLY what you meant?

  6. #6
    Registered User
    Join Date
    08-19-2017
    Location
    Hawaii
    MS-Off Ver
    2013
    Posts
    27

    Re: Formula for conditional formatting

    Sorry, I just noticed it.
    Yes, it does look odd and that is correct. Is it possible?

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,023

    Re: Formula for conditional formatting

    Try all the conditions now....
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    08-19-2017
    Location
    Hawaii
    MS-Off Ver
    2013
    Posts
    27

    Re: Formula for conditional formatting

    Thank You Very Much Glenn.

+ 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