Array formula for a rolling AVERAGEIF formula?

Question

I have a working formula that I need to drag to autofill down a column and want to make it into an array formula:

=AVERAGEIF(INDIRECT("A2:A"&ROW()), ">=0",INDIRECT("A2:A"&ROW()))

So if you put this formula in column B it will take the values in column A and continually average them going down, skipping any values that are less than 0. Here is an example screenshot: https://i.imgur.com/nRq8hAH.png

How can I make an array formula for this?

This formula comes close but I couldn't figure out how to add the ">=0" conditional:

=ArrayFormula(IF(LEN(A2:A),SUMIF(ROW(A2:A),"<="&ROW(A2:A),A2:A)/COUNTIF(ROW(A2:A),"<="&ROW(A2:A)),))

I can demo a way to do it if you make your spreadsheet editable instead of just view only. — MattKing

Calculuswhiz Calculuswhiz · Accepted Answer · 2020-09-14T20:32:22

Here's a single formula that can go into B2 (no need to drag), but it's fairly complicated:

=ArrayFormula(IFERROR(IF(LEN(A2:A),MMULT(TRANSPOSE((SEQUENCE(COUNTA(A2:A),1,2)<=TRANSPOSE(SEQUENCE(COUNTA(A2:A),1,2)))*FILTER(A2:A,LEN(A2:A))),--(FILTER(A2:A,LEN(A2:A))>0))/COUNTIFS(SEQUENCE(COUNTA(A2:A)),"<="&SEQUENCE(COUNTA(A2:A)),FILTER(A2:A,LEN(A2:A)),">=0"),"")))

Readable:

=ArrayFormula(IFERROR(
    IF(
        LEN(A2:A),
        MMULT(
            TRANSPOSE(
                (SEQUENCE(COUNTA(A2:A),1,2)<=
                    TRANSPOSE(SEQUENCE(COUNTA(A2:A),1,2))
                )*FILTER(A2:A,LEN(A2:A))
            ),
            --(FILTER(A2:A,LEN(A2:A))>0)
        )/
        COUNTIFS(
            SEQUENCE(COUNTA(A2:A)),
            "<="&SEQUENCE(COUNTA(A2:A)),
            FILTER(A2:A,LEN(A2:A)),
            ">=0"
        ),
        ""
    )
))

How?

We can achieve a running sum using MMULT on a Lower Triangular Matrix of size COUNTA(A2:A) of all 1's and all non blanks of A2:A, which we filter out if the number is negative. In this case, it produces {2;2;6;6;6;6}.
The COUNTIFS() produces an array of the number of elements we want to divide by. Here, it's {1;1;2;2;3;4}
Then ignore any blanks at the with IF.
Blank out any errors with IFERROR. (#DIV/0! errors can happen if the leading numbers are negative.)

Array formula for a rolling AVERAGEIF formula?

2 Answers

Readable:

How?