median and interquartile range of cells containing same value in two other columns

I don't know enough about statistics to do the 'Interquartile range' or 'Skew'.

MEDIAN I understand. See if this gets you started.

Array enter this in G3, fill down and across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

Edit I'll attempt the 'Interquartile range'. I am not sure I have the concept though. Array enter this in M3, fill down and across.

Formula:

Please Login or Register  to view this content.

Originally Posted by FlameRetired

=IFERROR(MMULT(QUARTILE(IF(($L3=$A$2:$A$31)*(M$2=$B$2:$B$31),$C$2:$C$31),{0,4}),{-1;1}),"")

Hello Dave

My understanding is that the interquartile range is the difference between 3rd and 1st quartile, in which case the above works with {1,3} in place of {0,4}

You could also get the same result using an AGGREGATE function which doesn't require "array entry", i.e.

=IFERROR(SUM(AGGREGATE(17,6,$C$2:$C$31/($L3=$A$2:$A$31)/(M$2=$B$2:$B$31),{1,3})*{-1,1}),"")

DDLL
Thank you for clarifying the concept.

cdad you are welcome. Thank you for the feedback and for marking your thread Solved.

You can add another condition to exclude blanks, i.e. this formula in G3

=IFERROR(IF(ISNUMBER(G$2),MIN(IF(($F3=$A$2:$A$31)*(G$2=$B$2:$B$31)*($C$2:$C$31<>""),$C$2:$C$31)),MIN(IF(($F3=$A$2:$A$31)*($C$2:$C$31<>""),$C$2:$C$31))),"")

confirmed with CTRL+SHIFT+ENTER and copied across and down

cdad since the uploads don't include those ranges we really can't advise you on much beyond encouraging you not to reference whole columns. There are exceptions. This is not one of them. That formula is multiplying values over more than 1 million rows ... repeatedly. That makes Excel work too hard and will slow performance.

Try another upload, Show us to what ranges and values these formulas refer.