Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Restricted Percentile (XL 97 / 2K)

    I have a large "flat file" of data, that is summarized and presented in a Pivot Table. In the PT (and in the XL 2K version, the Pivot Chart) I am able to show an average value as well as standard deviation of the characteristic of interest. I have charted the average and the average +/- one sd. What I would prefer is to chart the average and the 25th and 75th percentile break-points. This is easy enough if I am including all data in the pivot table just by applying the PERCENTILE worksheet function to the entire data set, but when I restrict the data to some subset (by month, year, etc) PERCENTILE continues to use all the data as the argument to the function.

    Is there any way to restrict PERCENTILE so it only considers a sub-set of the data? I have thought about a VBA routine that examined the data on every change in the worksheet (or better, on every change in the PT field selections), and ran through the data set tossing values into a VBA Array if they meet the PT field qualifiers, and ignoring them if they don't, then applying PERCENTILE to the resulting array (I suppose I would have to re-dim it down to the number of entries it had received, but that's a quibble). This sounds like a lot of work (and overhead...), and I suspect it will be MUCH slower than anything built in (I have 44K rows of data - hourly pricing for 5 years...)

    Any other suggestions???

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

    Re: Restricted Percentile (XL 97 / 2K)

    You can probably use array formulas, but it would be nice if you could provide a (much reduced) example file to experiment with.

  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: Restricted Percentile (XL 97 / 2K)

    Instead of an array, if you can "live with" the intermediate column, you could add the column to "get" only the values of interest, something like:
    =if(A2="Test",D2,"")

    Then do your percentile on this column. The percentile will ignore the text and only use the numbers.

    Creating the intermediate column is much quicker than arrays, since it only creates it once. with arrays, each formula has to internally create the array which slows excel down dramatically.

    Steve

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Restricted Percentile (XL 97 / 2K)

    Steve and Hans - thank you both for your help. I tried both methods, and Steve is right - the "extra column" approach is much faster, although aesthetically I prefer the array formula <img src=/S/artist.gif border=0 alt=artist width=34 height=29>. In this case, I think the speed will be the deciding factor, as all the workings can be hidden on a separate sheet

    I have attached the file I used to test the two alternatives - it forces 1000 changes in criteria value per cycle, and a user-selectable (in the module - I didn't bother with a fancy UI) number of cycles - first on the array formula method, and then on the column calculation method.

Posting Permissions

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