MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Advanced Advanced Filter Criteria


Posted by Melanie Swarner on October 19, 2001 6:02 AM

I'm trying to use an advanced filter to filter out records. The problem is, I have a lot of columns and a lot of different combinations of criteria. I have a row with tolerance levels. I want it to look at the database and decide if the *absolute value* of the cell is greater than the tolerance. I know I can do this by making 2 rows one that says >Tolerance and another that says <-Tolerance, but there's more. 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.

Is there a way to put a formula as criteria. For instance, can I say If abs(DatabaseCell) > Tolerance Keep? I'm willing to use VB if it won't work with Advanced Filter, but I wouldn't know where to begin.

Thanks for any help!


Posted by Mark W. on October 19, 2001 6:20 AM

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.

Posted by Juan Pablo on October 19, 2001 6:38 AM

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


Posted by Mark W. on October 19, 2001 6:47 AM

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


Posted by Mark W. on October 19, 2001 7:05 AM

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.


Posted by Juan Pablo on October 19, 2001 7:08 AM

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


Posted by Mark W. on October 19, 2001 7:12 AM

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


Posted by Melanie Swarner on October 19, 2001 7:28 AM

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


Posted by Mark W. on October 19, 2001 7:32 AM

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


Posted by Melanie Swarner on October 19, 2001 8:17 AM

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.


Posted by Juan Pablo on October 19, 2001 8:23 AM

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.


Posted by Mark W. on October 19, 2001 8:28 AM

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.


Posted by Mark W. on October 19, 2001 9:02 AM

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...