Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Advanced filters (+/-5%)

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!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default Advanced filters (+/-5%)

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

--
Regards
Roger Govier

"Hotel_guy" wrote in message
...
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!


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default Advanced filters (+/-5%)

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.

--
Thanks,
Shane Devenshire


"Hotel_guy" wrote:

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!

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,276
Default Advanced filters (+/-5%)

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


"Hotel_guy" wrote:

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!

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default Advanced filters (+/-5%)

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.

--
Thanks,
Shane Devenshire


"Hotel_guy" wrote:

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!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Advanced filters. Hector Excel Discussion (Misc queries) 0 May 22nd 08 01:16 PM
Advanced Filters CJLuke Excel Discussion (Misc queries) 2 March 22nd 07 10:05 AM
Advanced Filters Louise Excel Worksheet Functions 9 October 28th 05 11:52 AM
advanced filters jiwolf Excel Worksheet Functions 4 October 18th 05 06:08 PM
Advanced Filters Louise Excel Worksheet Functions 3 September 26th 05 11:57 AM


All times are GMT +1. The time now is 05:41 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"