Nested IF functions in Median causes error in array

Question

I have looked at a few solutions in this forum and tried their answers, but not been able to reach a solution. I have a data set I want to determine a conditional median for.

Sample Dataset:

Desired Outcome: I want to find the median of Ages, if the BD column has an entry of FSGS or MCD.

My current formula is:

{=MEDIAN(IF(BD="MCD",IF(BD="FSGS",Age)))}

But I keep getting a #Num! error.

I have checked that the "age" field are formatted as numbers. The BD field are formatted as General (but I have gone through and retyped each to ensure there were no leading spaces etc)

I can't seem to work out the problem, can anyone can help??

Answer 1

Here are few ways to accomplish the desired output:

---

• Formula used in cell D2 --> Formula works with Excel 2021+ onwards.

=MEDIAN(FILTER(Age,MMULT(N(BD={"MCD","FSGS"}),{1;1})))

---

• Or, Formula used in cell D3 --> Formula works with MS365 onwards.

=MEDIAN(TOCOL(Age/((BD="MCD")+(BD="FSGS")),2))

---

• Or, Formula used in cell D4 --> Formula works with Excel 2021+ onwards.

=MEDIAN(FILTER(Age,1-ISNA(XMATCH(BD,{"MCD","FSGS"}))))

---

• Or, Using BYROW() with ETA LAMBDA --> Formula works with MS365 Office Insiders onwards.

=MEDIAN(FILTER(Age,BYROW(BD={"MCD","FSGS"},OR)))

---

• Or, Using IF() --> Formula works with Excel 2013+ onwards but based on ones Excel Version will need to hit CTRL+SHIFT+ENTER while exiting the edit mode.

=MEDIAN(IF((BD="MCD")+(BD="FSGS"),Age,""))

---

• Where:
• Age: =$A$2:$A$11
• BD : =$B$2:$B$11

---