title: Excel Incorrect Array Formula usage explanation
body: <p>This is my data</p>
  
  <pre><code>Region    Value
  East       9800
  East       5592
  East       6966
  West       5418
  </code></pre>
  
  <p>When I do </p>
  
  <pre><code>=AVERAGE(IF(A2:A5="East",B2:B5)) CTRL+SHIFT+ENTER
  </code></pre>
  
  <p>I get </p>
  
  <pre><code>7452.667
  </code></pre>
  
  <p>Which is the right answer. But when I do</p>
  
  <pre><code>    =AVERAGE(IF(A2:A5="East",B2:B5))  ENTER
  </code></pre>
  
  <p>I don't get an error. I get</p>
  
  <pre><code>6944
  </code></pre>
  
  <p>If I just typed</p>
  
  <pre><code> =IF(A2:A5="East",B2:B5)   ENTER
  </code></pre>
  
  <p>I get</p>
  
  <pre><code> 9800
  </code></pre>
  
  <p>So 6944 is not the average of 9800. What is Excel doing here?</p>
  
  <p>When I do</p>
  
  <pre><code>=IF(A2:A5="West",B2:B5)  ENTER
  </code></pre>
  
  <p>I get</p>
  
  <pre><code> FALSE
  </code></pre>
  
  <p>So it looked like IF is reading the first row.. but then something weird happens to Average(If())</p>
  
  <p>I have one/two question(s) and one prayer:</p>
  
  <p>Questions: What is excel doing to arrive at 6944? Why did I not get an error when I did ENTER instead of CTRL+SHIFT+ENTER and is there any way to remind excel to tell me to do the right thing when dealing with Arrays?</p>
  
  <p>Prayer: I can only hope that I have not produced a lot of garbage in the last few weeks when I have neglected to to CTRL-SHIFT-ENTER and done ENTER by mistake and not gotten errors and ran with what I got.</p>