I'm trying to calculate the Median since a pivot table won't work.
I have a number of conditons that i need to fulfill so i need a
={median(if(and(A:A=A2,B:B=B2,C:C=C2,D:D=D2),T:T,"")}
type formula.
Columns A, B, C and D have the criteria and T has the value that I need the Median of.
I have been able to produce a median with just 1 variable, but i'm only getting #n/a when i try more.
I have seen that an AND function doesn't work with an Array, so is there another way that I can calculate the mean based upon 4 different conditions?
Any Help would be greatly appreciated!
Ed
2 Answers
Array formula do not like AND or OR so use * and + respectively to turn the TRUE and FALSE of each of the Boolean test to 1 and 0 respectively.
So with * if any are FALSE it will be 0 and turn the whole to 0, where as with + if any are TRUE then it will be greater than 0 and the IF will return the TRUE result:
=median(if((A:A=A2)*(B:B=B2)*(C:C=C2)*(D:D=D2),T:T))
If you are using Google Sheet (If not, you should :) )
Above, can be achieved using combination of MEDIAN and FILTER functions.
FILTER(range, condition1, [condition2, ...])
=MEDIAN(FILTER(T:T, A:A=A2, B:B=B2, C:C=C2, D:D=D2)
It filters T:T based on the conditions provided next, then Median of the result is returned.
