Search code examples
arraysexcelcriteriamedianquartile

Excel: How to add multiple criterias to an array calculation of Median and 1st Quartile

I have a question regarding a big excel-file I am working on right now.

I have a long column of values (Column B) in a larger list of data. In Column C I have a connection to another values in the datalist that can be either TRUE or FALSE depending on different conditions.

enter image description here

I have managed to make two array functions that calculate the median and 1st quartile for the values in column B in which the value in column C is TRUE, using the following formulas. They calculate the median and first quartile of the values in Column B as long as the value in Column C of the corresponding line equals TRUE.

{=MEDIAN(IF($C$2:$C$11; $B$2:$B$11))}

{=QUARTILE(IF($C$2:$C$11; $B$2:$B$11);1)}

Now I want to add another condition to the calculation. Apart from having the value in Column C equals TRUE, I also just want to calculate the median and first quartile if the value in Column A equals "Measure 1", or any other dynamic value. I have tried to nest the AND function below but it doesn't work at all. 

{=MEDIAN(IF(AND($B$2:$B$11;$A$2:$A$11="Measure 1"); $C$2:$C$11))}

Would anyone be able to help me solving how I can add values to an array depending on multiple criterias and later calculate median and quartiles of that array?

Solution

  • You construct an array of the applicable values, and then apply the function to the array.

    Since these are array formulas, you must enter/confirm these formulas by holding down ctrl + shift while hitting enter. If you do this correctly, Excel will place braces {...} around the formula as observed in the formula bar

    So, for Median:

    =MEDIAN((IF((Condition_1=TRUE)*(Measure="Measure 1")*Value,(Condition_1=TRUE)*(Measure="Measure 1")*Value)))

    and for Quartile 1

    =QUARTILE((IF((Condition_1=TRUE)*(Measure="Measure 1")*Value,(Condition_1=TRUE)*(Measure="Measure 1")*Value)),1)

    Quartile is also one of the AGGREGATE arguments that can work on an array, so you could normally enter:

    =AGGREGATE(17,6,1/((Condition_1=TRUE)*(Measure = "Measure 1"))*Value,1)

    And, where the QUARTILE.INC quart argument = 2, that is the same as MEDIAN

    So for Median, you could use:

    =AGGREGATE(17,6,1/((Condition_1=TRUE)*(Measure = "Measure 1"))*Value,2)