title: Summation of an array of max values in excel?
body: <p>Suppose I have the following data in cells A1:A3</p>
  
  <pre><code>A1 = 1
  A2 = 3
  A3 = 5
  </code></pre>
  
  <p>I'd like to write a formula that sums the difference between the number in the cell and 2. If the difference is negative, then take 0. I tried the following CSE/array formula:</p>
  
  <pre><code>{=sum(max(A1:A3-2,0))}
  </code></pre>
  
  <p>Excel evaluates the formula as follows:</p>
  
  <pre><code>sum(max({1-2,3-2,5-2},0)) = sum(max({-1,1,3},0)) = sum(max(-1,1,3,0)) = sum(3) = 3
  </code></pre>
  
  <p>I'd like to write a formula that is evaluated as follows:</p>
  
  <pre><code>sum({max(-1,0),max(1,0),max(3,0)}) = sum({0,1,3}) = 4
  </code></pre>
  
  <p>Obviously one approach to this problem is to carry out a second computation in column B such that:</p>
  
  <pre><code> B1 = max(A1-2,0) = max(-1,0) = 0
   B2 = max(A2-2,0) = max( 1,0) = 1
   B3 = max(A3-2,0) = max( 3,0) = 3
  </code></pre>
  
  <p>then my target formula is:</p>
  
  <pre><code>= sum(B1:B3) = 4
  </code></pre>
  
  <p>However, I'd like to know: <strong>is there a way to do this in a single cell using array formula?</strong></p>
  
  <p>Thanks!</p>