here's my problem: I need to calculate the median from the following table:
V1 V2 Total
A 0 0
B 2 10
C 2 12
D 2 19
E 2 22
A 2 4
B 1 12
D 1 0
C 2 8
A 0 10
D 1 15
A 2 12
B 2 10
E 1 16
Criteria are as follows:
- 'B', 'C', and 'D' from column V1
- not zero from column Total
- calculate median from column Total
Until now, the formula works perfectly:
=MEDIAN(IF(B2:B15={"B","C","D"},IF(NOT(D2:D15="0"),D2:D15)))
And now comes the hard part. It has to include another criteria, which is:
- 'A' from column V1 only if not 0 in column V2
I have no idea how to embed it in the code above, because it gives me various types of errors, depending on what I try.
CodePudding user response:
An idea using Microsoft365:
Formula in E2
:
=MEDIAN(FILTER(C2:C15,(ISNUMBER(FIND(A2:A15,"BCD")))*(C2:C15<>0) (A2:A15="A")*(B2:B15<>0)))