Advanced filters (+/-5%)

[Hotel_guy]

I'm wondering if it's possible to use the advanced filter function to create
a range option. Basically, I want to show all data which is between 5% over
a specific figure to 5% under a specific figure. I'm using Excel 2003. Is
this possible?

Thanks for your help!

 

[Roger Govier]

Hi

Yes you can.
Insert a couple of blank rows above your header row
Assuming the column that holds the values you want to filter upon is column
D and the first data value is in D4,
enter in A2
=D4>=your_value*0.95
enter in B2
=D4<=your_value*1.05

In Advanced Filter, set your criteria range as A1:B2

 

[ShaneDevenshire]

Hi,

Yes

Set up a Criteria Area such as

Results Results

56 <65

Where Results corresponds to a title in your data area, and 56 and 65
correspond to the 5% under and 5% over values. You amy want to change to >=
and <= but the idea is the same.

 

[Eduardo]

Hi, best way is to insert a column with the formula as follow

Let's say the amount you want to compare is in cell A1 and the values to
compare down from A2

=+IF(AND(B24>=D22,B24<=C22),1,0)
Then sort by 1 this column and you will have your values between that ranges

 

[ShaneDevenshire]

Hi again,

Although I gave you the easiest way to do this with Advanced Filter, there
is a second more complex approach - you can use a calculated criteria range.
If you haven't got what you want already, post back and I will detail this
approach, or you can check the help system for calculated critereia.