Medianif & cell reference operators

[kcbannon]

I having no luck in substituting cell references for actual operators or
independent variables in a MEDIANIF array. It has worked in simpler
functions like COUNTIF. Am I missing something?

In the table below, I want to find the median of the returns in B4:B7
subject to the values in A4:A7 being less than, greater than, equal to
or not equal to (operator in cell A2) a certain variable (value in cell
B2).

Oper Variable
= 5

Value Return
2.5 15.1%

5.0 12.2%

6.2 9.8%

7.3 21.3%

The way I think the formula should be written is:
=median(if(A3:A7=5,B3:B7,""))

However, I want to be able to manipulate the operator (A2) and the
independent variable (B2) by referring to the cells. This is where is
am having problems; the formula returns #Value!
=median(if(A3:A7,$A$2&$B$2),B3:B7,"")))

Tracing through the second formula, it appears that the comma after A7
marks the end of the Logical Test and "$A$2&$B$2" become the Value if
True.

Any help would be greatly appreciated.

 

[Scott]

Try the following... a bit of a lengthy array formula, but should work.
(For the symbols =, <=, >=, <, >, <>)

=MEDIAN(IF(A3:A6-$B$2=0,IF(ISERROR(FIND("=",$A$2)),"",B3:B6),IF(A3:A6-$B$2>0,IF(ISERROR(FIND(">",$A$2)),"",B3:B6),IF(ISERROR(FIND("<",$A$2)),"",B3:B6))))

You'll have to adjust the range. I tested in A3:A6.

Scott

 

[kcbannon]

Thanks, this helps, but it looks like I will still have to CTRL+H a
replacement within the formula every time I want to change the operator
(say from equals to "less than").

 

[Scott]

Sorry, what do you mean by this? The formula I gave should work for
whatever symbol (of those ones listed) that you put in A2. (Without
adjusting the formula at all)

It is limited to =, <, >, <=, >=, and <> though.

To re-iterate, there should be no need to modify the signs in the
formula. You should only have to change the value in A2. (In fact, it
might not hurt for you to have a validation list in A2 consisting of
those 6 values.)

Scott

 

[bplumhoff]

Hello,

I suggest to take Russell's MEDIANIF UDF:
http://www.bettersolutions.com/excel/EIK284/LN622911811.htm

HTH,
Bernd