title: Excel Calculate a running average on filtered data with criteria
body: <p>I am trying to calculate a running average on a filtered data table. All other posts online use Sumproduct(Subtotal) on the entire range but do not calculate a row by row running average</p>
  <p><strong>I am stuck on how to calculate columns C and D.</strong></p>
  <p>If <strong>column B (Score)</strong> &gt; 0, I want to sum and average it under <strong>column C (Average Win)</strong></p>
  <p>If <strong>column B (Score)</strong> &lt; 0, I want to sum and average it under <strong>column D (Average Loss)</strong></p>
  <p>The table is filterable by <strong>column A (Type)</strong> and the results should look as follows</p>
  <p><a href="https://i.stack.imgur.com/Dk8Vv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Dk8Vv.png" alt="enter image description here" /></a></p>
  <hr />
  <hr />
  <p>Progress so far:</p>
  <p>I have figured out how to calculate a Cumulative score based on filtered data. However this does not fully solve my problem. I appreciate any help!</p>
  <blockquote>
  <p>=SUBTOTAL(3,B3)*SUBTOTAL(9,B$3:B3)</p>
  </blockquote>
  <p><strong>SUBTOTAL(3,B3)</strong> checks if the current row is visible, <strong>SUBTOTAL(9,B$3:b3)</strong> sums the values.</p>
  <p><a href="https://i.stack.imgur.com/OBd0x.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OBd0x.png" alt="enter image description here" /></a></p>
  <hr />
  <hr />
  <h2>Final update needed</h2>
  <p>Jos - Thank you for your detailed explanation on how subtotal() works. I learned a ton through your explanation and will continue to study it. This is my first time being exposed to structured referencing so some of the syntax is a bit confusing to me still</p>
  <p>The last formula I need is a running win % column where a Win is defined by score &gt; 0. Please see the picture below</p>
  <p><a href="https://i.stack.imgur.com/GDzAF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GDzAF.png" alt="enter image description here" /></a></p>
  <p>My assumptions believe that the same formula would work, except that we average a 1 or 0 in each row instead of the [Score] column.
  Using the prior solution, why can't we modify the output of your prior solution to calculate a <strong>running win %</strong>?</p>
  <pre><code>[...] IF([Score]&gt;0,IF(ROW([Score])&lt;=ROW([@Score]),[Win])))),0)
  </code></pre>
  <p>Where [Win] is a helper column with the outputs 1 for win, 0 for loss.
  This could be done by saying</p>
  <pre><code>if([@score]&gt;0,1,0)
  </code></pre>
  <p>Instead of averaging out the actual @Score, this would average out a column of 1's and 0's with the desired output 0%, 50%, 66%, etc.</p>
  <hr />
  <p>I am aware that the solution I provided does not work but I am trying to embrace the correct logic. I still struggle to understand how these structured column references are calculated on a row by row basis.
  For example: <code>Average(If([Score]&gt;0,[Score])</code></p>
  <p>How is this calculated on a row by row basis? When A3 does <strong>If([Score] &gt; 0,)</strong>, does this equal <strong>If({-10}&gt;0)</strong>? When on A4, does <strong>If([Score]&gt;0)</strong> equal <strong>If({-10,20} &gt;0)</strong>? Thank you for your patience and help thus far.</p>