LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Junior Member
 
Posts: 2
Default Medianif & cell reference operators

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Nesting a sheet name reference within a cell reference??? Broyston Excel Discussion (Misc queries) 9 July 8th 08 08:35 PM
Using an offset formula for the reference in a relative reference Cuda Excel Worksheet Functions 6 November 15th 06 05:12 PM
How do I anchor a cell reference? CasaJay Excel Discussion (Misc queries) 3 June 7th 06 07:12 AM
Possible Lookup Table Karen Excel Worksheet Functions 5 June 8th 05 09:43 PM
how to create a variable column in cell reference Sampson Excel Worksheet Functions 3 February 21st 05 10:13 PM


All times are GMT +1. The time now is 06:53 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"