Advanced filters (+/-5%)

  • Thread starter Thread starter Hotel_guy
  • Start date Start date
H

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!
 
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
 
Hi,

Yes

Set up a Criteria Area such as

Results Results
56 <65
Click to expand...

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