Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2008
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to apply an accurate median calculation and 95th percentile calculation to this waiting list data. The problem is that because the data is all grouped under each wating cohort, it doesn't give an accurate calculation if I use the =MEDIAN() calc in excel - is there any other way of doing this, or will the data need to be extracted differently?
    Thanks
    Attached Files Attached Files

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I think you need to give an example of exactly what you want the Median for in relation to this data, and also an example
    with the answer you would expect. That would help in determining if the data needs to be in a different layout.
    Andrew

  3. #3
    New Lounger
    Join Date
    Nov 2008
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Andrew for your reply - i'm trying to calculate the median point of this waiting list. In the data attached it groups the patients waiting under each cohort, for example 40 under 0-1 week, 32 under 1-2 weeks etc Unfortunately in excel if I was to do a median calculation on this it would just be of these numbers.
    What I need is the median of all 40 0-1 waiters (as if they were listed out 000000000.......to 40), all 32 1-2 waiters etc but i'm not sure if there is a way in excel that I can change this data - I think there probably isn't and it will mean changing the extraction query if possible.
    Apologies though if this still doesn't make sense!

    Thanks

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I'm still not quite getting what the data list would be you want to find a median for.
    Can you show what you would want in the first row say over the first 3 cohorts to get the median.
    Andrew

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Woodbury, GA
    Posts
    15
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If I understand you correctly and you want the median wait of each group of wait times (0-1 & 2-3) for example, then you must list all forty "waiters" and the length of their waits to calculate the median. *The same would be true for each cohort.

  6. #6
    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
    I am not not sure what you are after. But if each row is a separate dataset and the first set has 40 within 0-1 week, 32 within 1-2 weeks, etc then you can create intermediate rows to calculate the subtotal for each period. Then estimate the median (50 percentile) and the 95-percentile by just mulitplying the total number by 50% or 95% and using a match to see where that value is in the dataset...

    See attached file

    Steve
    Attached Files Attached Files

Posting Permissions

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