Using ‘AND’ in Conditional Formatting (v97 SR2)

Topic

New Reply

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.

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote