+ Reply to Thread
Results 1 to 9 of 9

Conditional format with multiple criteria

  1. #1
    Registered User
    Join Date
    12-30-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Conditional format with multiple criteria

    I've tried just about everything I can think of, but I must be missing something.
    I need to conditionally format a cell to turn green if three different quantities hit a minimum number, and one of those quantities hits a secondary and/or tertiary target.

    For example.
    Single
    Pants minimum = 6
    Shirts minimum = 12
    Coats minimum = 3

    Double
    Pants = 12
    Shirts = 24
    Coats = 6

    Triple
    Pants = 18
    Shirts = 36
    Coats = 12

    So double shirt cell should only turn green if a single target is accomplished on all three items, and the second level for that shirt target is also reached.

    I currently have it laid out as followsCaptureddgd.JPG

    I need only the cells to highlight if those numbers have been hit, and only if they hit a minimum of a single in all three items. So ideally the first three under single should turn green with the And formula which i have nailed down, however I can't get the double or the triple cells to work.
    Last edited by mxsc; 04-30-2015 at 10:34 AM.

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional format with multiple criteria

    for the rule in double

    AND( min(A2=6), min(a3)=12, mon(A4)=3, min(b2)=12, min(b3)=24, min(b4)=6)
    and for triple
    AND( min(A2=6), min(a3)=12, mon(A4)=3, min(b2)=12, min(b3)=24, min(b4)=6, min(c2)=18,min(c3)=36,min(c4)=12)
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    12-30-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Re: Conditional format with multiple criteria

    Darn I think i missed an important bit of information.

    I have the totals rolling up in one spot.

    so that formula would end up referencing the same cells i believe.
    I was thinking that I would end up having to use an IF/AND combo, but can't get the logic down.

    So how it works is the quantities roll up in cells A 6, 7 and 8
    and the grids turn green as the numbers hit the minimums so long as all three categories have hit the minimum number.
    There are actually two grids, as there are two levels of applicable discounts, one where you don't have to hit every category to qualify, and one where you do.

    I really hope that makes sense.

    Would it be easier to reference two separate cell ranges for this?

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional format with multiple criteria

    A bit of clarification

    would it be true that
    for column A to turn green

    all three must be in the ratio of 6:12:3 with the minimum being 6,12,3
    so if A6 instead of 6 is say 9 - then what must the other 2 number be - still 12 and 3 or higher as a proportion of the 9
    ie 18 and 4.5

    for column B
    the numbers must be a multiple of column A
    so simply 2X
    and for column C
    the numbers must be a multiple of column A
    so simply 3X

    Once we have the rules for column A
    then the AND would be for column B
    just
    AND( B2/A2>2 , B3/A3>2 , B4/A4>2, and then the three min for column A)

  5. #5
    Registered User
    Join Date
    12-30-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Re: Conditional format with multiple criteria

    Example for forum.xlsx

    In the example I gave that would be true,

    However for the actual requirements are not exact,

    as in it goes from 12 to 25, then to 72.
    where another category goes from 24 to 40 then to 80

    Ah why am i being so paranoid,

    Here is the way its set up. (see attachment)

    The bottom numbers would reference totals from another page and automatically roll up.
    However right now I have them as basic so as to test the conditional formatting.

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional format with multiple criteria

    Sorry to keep asking

    We need to know the rules to apply the conditional formatting
    The order quantity
    Are you testing against the table to see if it qualifies for a discount
    is it H9,10,11 you want to conditionally format
    and test against it being Tier one or two

    So this order qualifies as single - because they are all over the minimun for single - BUT do not match the double or triple

    Now it wont turn Green because NONE of the elements meet the double or the triple requirement

    if the numbers were - changed to (note 37)
    21
    37
    71

    it would because
    Helmets = above the single
    Boots = above the triple
    Goggles = above the double

    Would it also be green
    IF two of the elements where above the triple minimum and the other one above the single minimum
    Helmets = 25
    Boots = 37
    Goggles = 73



    or have completely got this wrong !!!!

  7. #7
    Registered User
    Join Date
    12-30-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Re: Conditional format with multiple criteria

    Cells in the single double triple grid need to change color based on value in the helmet boot goggle order qty's.

    Tier two needs to change color on the grid if and only if a single quantity minimum is met on all three categories. So obviously if you want a single in the second tier those minimums must be hit so they will all turn green under single.

    You do not need to hit all of the double quantities or triple quantities to qualify for those discounts. You just need to purchase the single minimum in all three.

    For example if someone orders enough for a single on helmets, a double on boots, and a triple on goggles, the helmets would green under single, the boots would be green for the double, and the goggles would be green for the triple.

  8. #8
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Conditional format with multiple criteria

    Tier two needs to change color on the grid if and only if a single quantity minimum is met on all three categories. So obviously if you want a single in the second tier those minimums must be hit so they will all turn green under single.
    So for column K ALL three have to be Hit
    and for column C any of the 3 can be hit


    i have removed all your conditional formatting
    and replaced with just two rules

    for Tier 2
    =AND($H$8>=K$3, $H$9>=K$4, $H$10>=K$5)
    applied to =$K$3:$M$5

    For Tier 1
    =C3<=$H8
    applied to =$C$3:$E$5

    see if that works as you expect
    see attached
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-30-2014
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    31

    Re: Conditional format with multiple criteria

    Thanks for all your help.

    I figured out a simpler way to work around it while still accomplishing basically what i needed to do.

    Which was to change the conditional format in the double and triple on tier 2 to set different values.

    For example

    =and(L24>=40,L25>=6,L26>=12)
    =and(L24>=24,L25>=12,L26>=12)
    Etc
    Where all 6 of those cells have a different conditional format.

    The people that will be looking at it will just have to deal with progressive green coloring.

    I appreciate your time on this.

+ 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. [SOLVED] Conditional format based on multiple criteria
    By Kandy1084 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-24-2014, 01:36 AM
  2. Replies: 4
    Last Post: 12-17-2011, 06:11 PM
  3. Conditional Format Multiple Criteria VBA
    By SamsamF in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-21-2011, 08:45 AM
  4. [SOLVED] Conditional Format based on multiple criteria
    By maw230 in forum Excel General
    Replies: 9
    Last Post: 02-16-2010, 06:24 PM
  5. multiple criteria conditional format with date proximity
    By B. Baumgartner in forum Excel General
    Replies: 3
    Last Post: 01-13-2007, 08:31 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