Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced filters. | Excel Discussion (Misc queries) | |||
Advanced Filters | Excel Discussion (Misc queries) | |||
Advanced Filters | Excel Worksheet Functions | |||
advanced filters | Excel Worksheet Functions | |||
Advanced Filters | Excel Worksheet Functions |