Search code examples
excelexcel-formulasumifs

Excel Calculate a running average on filtered data with criteria

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

enter image description here

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.

enter image description here

Final update needed

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

The last formula I need is a running win % column where a Win is defined by score > 0. Please see the picture below

enter image description here

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 running win %?

[...] IF([Score]>0,IF(ROW([Score])<=ROW([@Score]),[Win])))),0)

Where [Win] is a helper column with the outputs 1 for win, 0 for loss. This could be done by saying

if([@score]>0,1,0)

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.

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: Average(If([Score]>0,[Score])

How is this calculated on a row by row basis? When A3 does If([Score] > 0,), does this equal If({-10}>0)? When on A4, does If([Score]>0) equal If({-10,20} >0)? Thank you for your patience and help thus far.

Solution

  • 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:

    =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.

    Explanation:

    Using the data you provided and assuming:

    1. The table's top-left cell is in A1
    2. The table is filtered on the Type column for "A"

    In order to determine which rows are filtered, we must pass an array of range references - i.e. for each cell within a chosen column of the table - to the SUBTOTAL function. It's a touch unfortunate that such an array of range references can only be generated via a volatile function (INDIRECT or OFFSET), but here, unless we resort to helper columns, we are left with no choice.

    INDEX([Score],1)

    simply returns a range reference to the first cell within the Score column. When using Excel tables, it's preferable not to write formulas which include a mixture of structured and non-structured referencing, even if that results in slightly longer expressions. So here, for example, we would not reference A2 within the formula.

    ROW([Score])-MIN(ROW([Score]))

    generates an array of integers from 0 up to one fewer than the number of rows in the table, i.e.

    {0;1;2;3;4}

    and so

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

    becomes

    =IFERROR(AVERAGE(IF(SUBTOTAL(3,OFFSET(A2,{0;1;2;3;4},)),IF([Score]>0,IF(ROW([Score])<=ROW([@Score]),[Score])))),0)

    OFFSET then generates an array of range references (though note that you will not be able to 'see' this step within the Evaluate Formula window - rather, an array of #VALUE! errors is displayed):

    =IFERROR(AVERAGE(IF(SUBTOTAL(3,{A2;A3;A4;A5;A6}),IF([Score]>0,IF(ROW([Score])<=ROW([@Score]),[Score])))),0)

    SUBTOTAL then determines which of these range references is filtered (note that care must be given here to the choice of first parameter), returning the relevant Boolean, so that:

    SUBTOTAL(3,{A2;A3;A4;A5;A6})

    resolves to:

    {1;1;1;0;1}

    And so we now have:

    =IFERROR(AVERAGE(IF({1;1;1;0;1},IF([Score]>0,IF(ROW([Score])<=ROW([@Score]),[Score])))),0)

    and the rest is straightforward.