Median based on criteria of another cell

[bloodybrit90]

{=MEDIAN(IF('textexport(2)'!J:J>0,'textexport(2)'!F:F))}

I keep receiving 0 when using this formula. I want to take the median of values in column F if the corresponding values in colum J are above 0.

Any ideas why this does not work.

 

[jeffreybrown]

It worked for me.

Did you enter the {} manually or did you commit the formula with Ctrl + Shift + Enter

 

[pplstuff]

Tested, works for me (using D:D and specific ranges). Use put {} in your example, I'm assuming you hit crtl+shift+enter?

 

[barry houdini]

Do you have >0 values in column F where column J is blank or zero? If there are many of those then you'll get zero as the median, you can exclude blanks like this

=MEDIAN(IF('textexport(2)'!J:J>0,IF('textexport(2)'!F:F<>"",'textexport(2)'!F:F)))

 

[bloodybrit90]

Yes I hit ctrl+shirt+enter.column J has the following array formula =IF(OR($B4&$I4=Sheet1!$F$90:$F$96),'textexport(2)'!$E4,""). would that effect the results?

The formula worked when referencing another column that did not have an array formula.

 

[barry houdini]

OK, you probably need something like the formula I posted except with the IF excluding "" in column J, i.e.

=MEDIAN(IF('textexport(2)'!J:J>0,IF('textexport(2)'!J:J<>"",'textexport(2)'!F:F)))

 

[bloodybrit90]

Barry, Yes I do have above 0 values in cloumn F when J is blank.

 

[bloodybrit90]

Cheers mate.

The formula you provided worked.