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. What is the most elegant and efficient way to produce these? I need something analogous to AVERAGEIFS.
Attached is filleted dataset, procedure date in sheet data! Column A, then age in column K. Calcs all set up to go on sheet calcs!
Presumably easiest using QUARTILE (Array, quart) liked with some IF statement but its just beyond me.. Anyone done this before? Help much appreciated.. Thanks, Mike
3 Replies
You may use AGGREGATE with first parameter 17 to calculate quartile with criteria. Second parameter 6 says to ignore all errors and in third parameters in 1/(...) we return errors (ignored) if dates are out of ranges and values otherwise.
=IFERROR(AGGREGATE(17,6,1/(Data!$A$2:$A$5030>=DATE(H41,1,1))/(Data!$A$2:$A$5030<=DATE(H41,12,31))*Data!$K$2:$K$5030,2),"")Please check in attached file.
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.
