title: "Non-contiguous" range specification in formula
body: <p>[2018-08-01: See new material near bottom of post.]</p>
  
  <p>I just ran across a Q-n-A <a href="https://stackoverflow.com/questions/41652483/percentile-formula-error-in-vba">here</a> that included an example of a function referencing a range using a specification I had not seen before. I played around with it a little and discovered that most Excel functions will return a value when using this kind of reference:</p>
  
  <pre><code>=SUM(A1:A5:C1:C5:E1:E2:F3:F4)
  </code></pre>
  
  <p>Notice all the ":"s in there.</p>
  
  <p>With the cursor in the formula bar, those non-contiguous cells are highlighted, <strong>as if</strong> only those cells would be totaled. However, when I experimented, I discovered that it is equivalent to this formula:</p>
  
  <pre><code>=SUM(A1:F5)
  </code></pre>
  
  <p>However, this formula does what I expect:</p>
  
  <pre><code>=SUM(A1:A5,C1:C5,E1:E2,F3:F4)
  </code></pre>
  
  <p>Example: I have the range A1:F5 defined as:</p>
  
  <pre>--  A   B   C   D   E   F
  
  1   1   2   3   4   5   6
  
  2   2   4   6   8   10  12
  
  3   3   6   9   12  15  18
  
  4   4   8   12  16  20  24
  
  5   5   10  15  20  25  30 </pre>
  
  <p>The formula results are:</p>
  
  <p>=SUM(A1:F5)  is 315</p>
  
  <p>=SUM(A1:A5:C1:C5:E1:E2:F3:F4) is 315</p>
  
  <p>=SUM(A1:A5,C1:C5,E1:E2,F3:F4) is 117</p>
  
  <p>This illustrates my point that the first two seem to be equivalent, and the third "correct" way of defining a discontiguous range gives a different result (but what I would expect).</p>
  
  <p>I looked around here and elsewhere on the Internet and found no relevant discussion.</p>
  
  <p>So, my questions:</p>
  
  <ol>
  <li>Is that odd range reference of any real use?</li>
  <li>is it really a valid sort of range reference?</li>
  </ol>
  
  <hr>
  
  <p>[New material 2018-08-01]</p>
  
  <p>@YowE3K wondered what would happen with an <strong>odd</strong> number of cells. Here is my example:</p>
  
  <pre>--  A   B   C  
  
  1   1   1   1   
  
  2   1   1   1   
  
  3   1   1   1   
  
  4   1   1   1   </pre>
  
  <p>Now, in a faraway cell, I enter this formula:</p>
  
  <pre><code>=SUM(A1:B3:$C$2)
  </code></pre>
  
  <p>Then if I drag the formula around, it creates interesting results, basically the 3-row, 2-column range extended to a rectangular range that always includes the fixed cell. The smallest sum you can ever get in this example is 3. Try it and see.</p>
  
  <p>This shows a glimmer of something useful, though I haven't come up with a concrete use for it yet.</p>