Advanced Advanced Filter Criteria

Suppose your database consists of 1 column, "Tolerance",
and that it's values are: {1;-4;-3;5}

Furthermore, let's say that the tolerance value
that you want for your Autofilter is +/- 3, and
the value of X is 2. So you want to show records
with an ABS greater than 3 or those records with
with an ABS greater than X (which is 2).

Let's create a computed criteria in D1:E3. First,
enter, =ABS(Tolerance)>3, into cell D2. Next,
enter, =ABS(Tolerance)>2, into cell E3.

This criteria will display {-4;-3;5}. {-4;5} meet
the tolerance requirement, =ABS(Tolerance)>3. {-3}
is displayed because it's absolute value is greater
than X.

You CAN use formulas. Take for example this data, put in A1:C8

{"Answer","Tolerance","Other Thing";"A",-2,1.74;"B",-5,1.33;"C",3,-0.16;"D",-1,2.71;"E",-3,-1.39;"F",2,-0.21;"G",0,2.39}

What i wanna do is filter ABS(Tolerance) > 1 AND ABS(Other Thing)>0.5. Note that i should get results A, B and E.

Now in A12 put
=ABS(B2)>1
and in B12 put
=ABS(C2)>0.5

Select Advanced filter, Copy somewhere else, in range put $A$1:$E$8, in Criteria put $A$11:$B$12 (Notice the "empty row" 11 used in the Criteria range) and finally select A15 as your destination range, you should get the expected results.

Juan Pablo

Wow... this sounds familiar (nt) :)

Doesn't Melanie Want an OR condition?

Your criteria only displays records that meet the
tolerance and are greater than X. So, it won't
display all records that meet the tolerance.

It think it's an AND

She wrote...

Of the records that are outside of tolerance levels, I want to keep ONLY the ones where another cell's *absolute value* is > than X.

That is, as i understand it, first filter tolerance and then of those only show > X, am i incorrect ?

And Mark, thanks for your patience to us all mortals... :)

Juan Pablo

Re: It think it's an AND

> am i incorrect?

Don't know... Melanie will need to clarify.
At times, describing boolean logic without
a truth table can be a challenge! She wrote... Of the records that are outside of tolerance levels, I want to keep ONLY the ones where another cell's *absolute value* is > than X. That is, as i understand it, first filter tolerance and then of those only show > X, am i incorrect ? And Mark, thanks for your patience to us all mortals... :) : Your criteria only displays records that meet the

Mark and Juan - One more question...

Wow guys, that's deranged - I'm impressed! :-) Where on earth did you learn how to do this? Is there a great book or some hidden documentation somewhere?

So I should just continue with my OR statements diagonally in my Criteria range, right?

Thanks for taking the time to answer my question! You CAN use formulas. Take for example this data, put in A1:C8

AND-ing and OR-ing

In Excel criteria... OR-ing is accomplished with
the inclusion of additional rows. AND-ing is
accomplished with the inclusion of additional
columns. See the Excel Help Index topic for
"Examples of advanced filter criteria". Wow guys, that's deranged - I'm impressed! :-) Where on earth did you learn how to do this? Is there a great book or some hidden documentation somewhere? So I should just continue with my OR statements diagonally in my Criteria range, right? Thanks for taking the time to answer my question! : You CAN use formulas. Take for example this data, put in A1:C8 : {"Answer","Tolerance","Other Thing";"A",-2,1.74;"B",-5,1.33;"C",3,-0.16;"D",-1,2.71;"E",-3,-1.39;"F",2,-0.21;"G",0,2.39} : What i wanna do is filter ABS(Tolerance) > 1 AND ABS(Other Thing)>0.5. Note that i should get results A, B and E. : Now in A12 put

Juan wins the prize....

In this specific example, I was looking for the rows where (cell1 is out of tolerance) AND (cell2 is out of tolerance). I need to use OR logic in other instances, though.

What does the blank line in the criteria range do?

Thanks again! > am i incorrect? Don't know... Melanie will need to clarify.

Enable use of formulas. (NT)

What does the blank line in the criteria range do? Thanks again! : > am i incorrect? : Don't know... Melanie will need to clarify.

Computed criteria...

> What does the blank line in the criteria range do?

I assume that you're referring to the 1st row...
Computed criteria don't use field names in the
1st row; however, the row must still be included
in the criteria range. For more on computed
criteria see the last paragraph, "Conditions
created as the result of a formula", of the
Excel Help topic for "Examples of advanced
filter criteria". In this specific example, I was looking for the rows where (cell1 is out of tolerance) AND (cell2 is out of tolerance). I need to use OR logic in other instances, though. What does the blank line in the criteria range do? Thanks again! : > am i incorrect? : Don't know... Melanie will need to clarify.

One more thing about computed criteria...

The formulas used in a computed criteria should
evalute to TRUE or FALSE and; therefore, involve
a relational (comparison) operator. > What does the blank line in the criteria range do? I assume that you're referring to the 1st row...