title: Determining median values with nested IF and AND array formulas
body: <p>I'm trying to create two similar array formulas: </p>
  
  <p>One is to determine the median value of the cells in a column (B), but only if there are both corresponding blank values in column J, and there are numeric values in column D. (D and J are in separate sheets.)</p>
  
  <p>The other formula is the do the same thing, except it will only determine the median for the cells in B for which the corresponding cells in column J are not blank.</p>
  
  <p>I should note I am making sure to enter them in with command-shift-enter, since they are array formulas. However, it's coming out to zero for both of them. I've tried for a long time and can't figure out why. I'm new to Excel formulas, so please let me know if I should clarify my question.</p>
  
  <p>First formula:</p>
  
  <pre><code>=MEDIAN(IF(
  
   AND('Raw Data'!$J$3:'Raw Data'!$J$999="", ISNUMBER('Raw Data'!$D$3:'Raw Data'!$D$999)),
  
   B$6:B$1002))
  </code></pre>
  
  <p>Second formula:</p>
  
  <pre><code>=MEDIAN(IF(
  
   AND(NOT(ISBLANK('Raw Data'!$J$3:'Raw Data'!$J$999)), ISNUMBER('Raw Data'!$D$3:'Raw Data'!$D$999)),
  
   B$6:B$1002))
  </code></pre>