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

I am stuck on how to calculate columns C and D.

If column B (Score) > 0, I want to sum and average it under column C (Average Win)

If column B (Score) < 0, I want to sum and average it under column D (Average Loss)

The table is filterable by column A (Type) and the results should look as follows

Progress so far:

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!

=SUBTOTAL(3,B3)*SUBTOTAL(9,B$3:B3)

SUBTOTAL(3,B3) checks if the current row is visible, SUBTOTAL(9,B$3:b3) sums the values.

CodePudding user response：

So, I would use averageifs().

=averageifs(B:B,B:B,">=1",A:A,"A")

is one example, note I have added the control of Type A in the example.

See:

CodePudding user response：

I disagree with your result for Average Loss for the last row of your unfiltered table (surely -9.33...?), but try this for Average Win:

Edit:

=IFERROR(AVERAGE(IF(SUBTOTAL(3,OFFSET(INDEX([Score],1),ROW([Score])-MIN(ROW([Score])),)),IF([Score]>0,IF(ROW([Score])<=ROW([@Score]),[Score])))),0)

Same formula for Average Loss, changing [Score]>0 to [Score]<0.