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

    Multiple range test (Excel 2000)

    I have a situation where I have 20 ranges of numbers, 10000 to 13575, 14500 to 15373, and so on. I have to figure out a way to tag these numbers within their larger, continuous range 10000 to 20000 in order to stratify them in a Pivot table report. I will have to do this numerous times. I will be receiving a master listing. I want to simplify the process of identifying these specific ranges each time I get the master listing. I suppose I will need some kind of separate cell with a formula that says "if in any of these ranges, give me text "Original" or something to that effect. I would use that data field to show the "Original" as a row element on my Table. And I imagine I would want to say something such as "Post implementation" for those assets outside those ranges.
    The other approach I am thinking about is embedding such logic in a Pivot table-creating macro, but I have not yet formalized the PT report format, and that would impact where and how one codes such stuff.
    Any suggestions as to how that formula should "look" in terms of nesting the ranges in an if statement and similar considerations will be appreciated. But I believe the nesting cannot go beyond 7 levels, then what? Also we *may* be on 2003 soon, if I can persuade certain "big dogs" that its worth it. So advise me in that direction as well.
    As always, thanks.

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

    Re: Multiple range test (Excel 2000)

    See if the attached does what you want. The lower and upper bounds of the ranges are in a table, and a SUMPRODUCT formula is used to determine if a number is within one of the ranges.

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

    Re: Multiple range test (Excel 2000)

    Thanks. I was just sitting here trying to think of a way to make SUMPRODUCT do it.
    This spreadhseet will be huge. I'm thinking it might be wiser in terms of processing time and so on to have this run via a macro that runs down column A and puts a text field in column z that is either Upload or Post, for instance. Then i wouldn't have alot of calculating to wait for as with a formula. So how would I specify those ranges inside a macro line(s)? I think it would be a FOR NEXT type structue, right?
    Thanks again.

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

    Re: Multiple range test (Excel 2000)

    You could set calculation to manual, with a command button on the sheet to recalculate, That way you would have to write very little code.

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

    Re: Multiple range test (Excel 2000)

    Agreed. Furthermore, third parties who would view these schedules would not be able to satisfy themselves as to the accuracy of a label if the macro put it in, but using a formula referring to a visible range of numbers is clearer. Plus, implementing your example revealed I needed a third criteria, the company number, since the real ranges overlap and one needs to include the company number as part of the test. Based on the sample, I just added another product argument based on a new company column and voila, it correctly distinguished the company parameter. So I'm going to stay with the sumproduct example. Thanks again.

Posting Permissions

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