# Thread: median & 95th percentile calculation

1. 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

2. 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.

3. 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. 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.

5. 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. 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

#### Posting Permissions

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