multiple condition Median If formula

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.

ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJoa3FoaWyFd4KOpqylrJmluaZ5wqilnaGknryvecyem6KZnmK2p3nFqKmmrZyW