Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Using 'AND' in Conditional Formatting (v97 SR2)

    I'd like to use Conditional Formatting on a cell to evaluate a cell's contents to check for two conditions:
    CellContents>=10% AND CellContents>=5000 like so,
    =AND(ABS(+G6)/I6>=0.1,ABS(+G6)/I6>=5000).
    I don't get an error message, but neither do I get the results I expected.
    I tried using the "Add>>" conditon, but it does not seem to force the AND.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Using 'AND' in Conditional Formatting (v97 SR2)

    The >=0.1 is redundant. If it is >= than 5000 than it is also >=0.1! So you are only checking whether it is >=5000

    What results are you expecting and what are you getting. What cell is this the cond format for?What is in G6 and I6?

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Using 'AND' in Conditional Formatting (v97 SR2)

    When I read your post I realized that not only had I mis-stated the question, but had also used the wrong criteria for my conditions.
    Anyway, I found what I was looking for with:
    =AND((ABS(+K6)/I6)>=0.1,ABS(+K6)>=5000)
    . . . just a wrong cell reference and misplaced parenthesis.
    BTW, I'm comparing the variance of a dollar amount difference between the actual in G6, the Budget in I6 and the Variance in K6 and want to test for really big variances. Some variances might be greater than 10% but less than $5k, which might not be as significant as those that meet both criteria. The Conditional Format for this test will allow me to sort more quickly through a 60 page report.

  4. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Birmingham, England
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using 'AND' in Conditional Formatting (v97 SR2)

    You don't fancy creating a new column with the AND condition, then using autofilter to list the ones you want? No need to page down thropugh zillions of records then?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •