Forum Discussion

LL19911695's avatar
LL19911695
Copper Contributor
Jul 18, 2024

#NUM! error for Median If Formula

I am using the following formula to get the median figure from X where AG=Central Functions, Y=Yes, and F=UK. Column X = currency. I keep getting a #NUM! error and can't figure out why...what am I missing?

 

=MEDIAN(IF(AG:AG="Central Functions*",IF(Y:Y="Yes",IF(F:F="UK",X:X))))

excel
Formulas and Functions
  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Jul 18, 2024

    just want to make sure you realize adding the * at the end of the "Central Functions" will not work as a wildcard in an = statement

    and maybe use the FILTER function instead
    Try
    =MEDIAN(FILTER(X:X, (LEFT(AG:AG,17)="Central Functions")*(Y:Y="Yes")*(F:F="UK"), ""))

    but if you don't have FILTER you can try:

    =MEDIAN(IF(LEFT(AG:AG,17)="Central Functions",IF(Y:Y="Yes",IF(F:F="UK",X:X))))

Resources