Forum Discussion
QUARTILE by date ranges - how to create a QUARTILEIFS equivalent
From a dataset of surgical procedures of around 5000 cases over 12 years, I need to extract the median and interquartile ranges for the the ages of patients, by year, to see how these have changed. W...
SergeiBaklan Many thanks and seems v. elegant but there is a fundamental problem, in that any blank lines in the dataset are counted as value 0 for age and included in the calculations. (most obvious in cells of calcs!F52 and F53 where the 25% interquartile age range is given as zero). This is skewing the entire ranges, medians and interquartiles alike. Is there another approach that will ignore the blank fields? Sorry, suspect this is quite a challenge..
To ignore blanks we may add one more criteria to the formula with ISNUMBER() on range:
=IFERROR(
AGGREGATE(
17,6,
1 /
(Data!$A$2:$A$5030>=DATE(H41,1,1)) /
(Data!$A$2:$A$5030<=DATE(H41,12,31)) /
ISNUMBER(Data!$K$2:$K$5030) *
Data!$K$2:$K$5030,
2
),
"")ISNUMBER() returns FALSE for any blank or text cells, thus we calculate only cells with values. Please check attached.