I have 2 columns of data where I am sorting column A smallest to largest. i.e. 1 44% 2 56% 3 52% 4 58% 5 62% 6 60% 7 65% Below this, I am calculating the four quartiles of Column A. i.e. Q1 = 2.5 What I would like to do is be able to calculate the median observation in column B per quartile. So, for the sake of simplicity, anything below 2.5 (1, 2) would have a median observation of 50%. Comprende? Of course, this would be easy enough if you were to do it by hand, but I am trying to record a macro to calculate the quartile, and then provide the median. I have a workbook with multiple sheets with various amounts of data, so a boilerplate formula won’t work. Anyone know how to do this, or if its even possible? Thanks
Definitely possible. Once you know the values of the four quartiles, use an if/then statement to get the actual quartile number on each line. (eg: on the column to the right of 44%, assuming Q1, Q2, Q3, and Q4 are the referenced cells for your quartile calcs and your percent column is column B, do something like: =IF(AND(B1>Q3,B1<=Q4),“Quartile 4”,IF(AND(B1>Q2,B1<=Q3,“Quartile 3”,IF(AND(B1>Q1,B1<=Q2),“Quartile 2”,IF(B1<=Q1,“Quartile 1”)))) THEN, once you have this new Quartile info along the columns, you can do a “median if” function. NOTE that this is one of those array type of formulas where you need to hit ctrl/shift/enter for it to work: Assuming column C is your Quartile output from the above and column B is your % column, {=MEDIAN(IF(C:C=C1,B:B))}
you can use the Quartile function too
DoubleDip Wrote: ------------------------------------------------------- > you can use the Quartile function too Wouldn’t that only give you the actual values of the respective quartiles? On my sentence, “Once you know the values of the four quartiles”, I assumed that this step would use the quartile function. But then to get what he asked for, the additional steps would be needed.