Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Nesting a sheet name reference within a cell reference??? | Excel Discussion (Misc queries) | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
How do I anchor a cell reference? | Excel Discussion (Misc queries) | |||
Possible Lookup Table | Excel Worksheet Functions | |||
how to create a variable column in cell reference | Excel Worksheet Functions |