Forum Discussion
QUARTILE by date ranges - how to create a QUARTILEIFS equivalent
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.
