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

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

> 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

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

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

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