Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Advanced filter criteria (Excel 2000)

    I dont' do this that often and Excel's help doesn't show this well: In the Advanced Filter criteria line, I want to filter on a field x for a number greater than or equal to 5400 and less than or equal to 6900. I am never sure whether the = goes first or last vs the >. Plus, would those be on two separate lines or in one cell below the field x in the criteria range? Thanks.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Advanced filter criteria (Excel 2000)

    1. As in the way you say and write it, the "greater than" or "less than" sign comes before the "equal" sign.
    2. You must use two columns for the field x in the criteria range, put the "greater than or equal" condition under one, and the "less than or equal" condition under the other.

    <table border=1><td>x</td><td>x</td><td>>=5400</td><td><=6900</td></table>
    Conditions in the same row, next to each other, are "AND" conditions: both must be satisfied. Conditions in the same column, one below the other, are "OR" conditions: at least one must be satisfied.

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Advanced filter criteria (Excel 2000)

    You can do it with 1 column if you use a formula.
    Leave the first row of the criteria blank and put in a formula like below (change A2 to the first cell of the row you want to compare:

    <blank>
    =AND(A2>=5400,A2<=6900)

    Steve

  4. #4
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Advanced filter criteria (Excel 2000)

    Not sure about the "row I want to compare" in the array column F has the set of numbers. That would make it =ANd(f2>=5400,<=6900), correct?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Advanced filter criteria (Excel 2000)

    If row1 has the headder so row 2 is the first row of data, yes row2 is what I was refering to: The equation needs both F2 references:
    =AND(F2>=5400,F2<=6900)

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •