Help with medianif function

[jrudner]

Hi, I know there is an existing median if thread, but it is not resolving the problem I have.

My formula below is as follows:

=MEDIAN(IF(Test!$G$2:$G$13215=B14,Test!$B$2:$B$13215))

I seem to be having difficulties referencing data on another worksheet. The formula is on the same sheet that B14 is on.

I enter it as an array format, but I get #NUM! as a result.

Any help? Thanks in advance,

JRudner

 

[Aladin Akyurek]

If B14 is not available in Test!$G$2:$G$13215, you'd get a #NUM! error...

What do you get with?...

=COUNTIF(Test!$G$2:$G$13215,B14)

 

[jrudner]

That gives me the correct number, 4428 rows that match.

 

[barry houdini]

Possibly numbers in column B are text formatted? Try

=MEDIAN(IF(Test!$G$2:$G$13215=B14,Test!$B$2:$B$13215+0))

 

[jrudner]

Barry, that gives me the #NUM! error as well.

 

[AusSteelMan]

Hi there,

I wish I could be more help, but thought I would let you know that I copy and pasted your formula as is and populated tab name and columns and got a result.

In fact, I got a result when the formula was entered as a CSE array formula and even as a regular formula.

Initially I thought it may have been because you did not have " ' " around your sheet name, but I was wrong with the example you gave. However if your sheet name has a SPACE in it, you will need them. eg

=MEDIAN(IF('test sheet'!$G$2:$G$13215=B14,'test sheet'!$B$2:$B$13215))

Hope this is useful, even if not directly helping.

ASM

 

[jrudner]

That worked! When I put the single quote around the worksheet name it worked properly. Strange, but thanks a ton!

JRudner

 

[AusSteelMan]

Matter of interest: what is your sheet name? Or at least does it have a SPACE?