How to get rolling average of first 5 non-ZERO values in a column

[frenchpress61]

Hello

New to the forum, I have struggled with this for hours now and hope someone can help. I'm trying to derive a function which generates a rolling average of the first five non-zero of a column.

Example column:

1
0
5
4
0
6
8
0
0
9
0
2


First average computed would be (1+5+4+6+8)/5 = 4.8
Second average would be (5+4+6+8+9)/5 = 6.4
and so on..

hope anyone can help!

 

[lrobbo314]

How about this?

RO
	A	B	C
1	1		4.8
2	0		6.4
3	5
4	4
5	0
6	6
7	8
8	0
9	0
10	9
11	0
12	2
Sheet6

Cell Formulas
Range	Formula
C1:C2	C1	=AVERAGE(INDEX($A$1:$A$12,AGGREGATE(15,6,ROW($A$1:$A$12)/($A$1:$A$12>0),ROW(INDIRECT(ROW(A1) & ":" & ROW(A1)+4)))))

 

[frenchpress61]

Thank you Irobbo314 this worked wonders!

 

[lrobbo314]

Guess it could really be reduced to this.

=AVERAGE(INDEX($A$1:$A$12,AGGREGATE(15,6,ROW($A$1:$A$12)/($A$1:$A$12>0),ROW(A1:A5))))

 

[frenchpress61]

@lrobbo314 any chance you have the time to explain the intuition behind the formulas? It was already able to solve my problem but hoping to try and wrap my head around how they all tie together.

 

[lrobbo314]

Sure.

At the core of the formula is the AGGREGATE function.

AGGREGATE(Function_Num, Options, Array, [k])

Function_Num: 15 for the SMALL option.

Options: 6 for ignore errors.

Array: Here is where the magic happens. This is actually made of of a numerator and a denominator. What is in the numerator spot, ROW(A1:A1), is really what is going to be returned. The denominator filters out the parts we don't want. So (A1:A12>0) will only return values that aren't 0. So, basically we're left with the result of division like this (1;2;3;4;5;6;7;8;9;10;11;12)/(True;False;True;True;False;True;True;False;False;True;False:True), which gives us (1; #DIV/0 ;3; 4; #DIV/0; 6; 7; #DIV/0; #DIV/0; 10; #DIV/0; 12). So, Aggregate ignores the errors and we're left with the array (1;3;4;6;7;10;12) which are the row numbers of values that are greater than 0.

k: Here we are telling the function which number(s) we want from the SMALL option. 1 would give us the first smallest, or in this case since we returned row numbers from the array, the first non-zero row. 2 would give us the second non-zero row and so on. You don't have to give k just a single number though. You can feed in an array. Which is what we are doing with ROW(A1:A5). That will return the first 5 non-zero numbers, 1,2,3,4,5. But when we drag the formula down, the ranges change to ROW(A2:A6), so it returns row numbers 2,3,4,5,6 of non-zero numbers.

But, we don't want to average the row numbers, which is why the results from the AGGREGATE function are feeding the ROW argument of the INDEX function. So, we can get the actual values. Then that it wrapped in the AVERAGE function to give the final answer.

 

[frenchpress61]

Amazing explanation, thanks so much you've been a big help!

 

[lrobbo314]

I know that you're using Excel 2016. I just wanted to post this here for fun.

Using the 365 functions, here is a LAMBDA function that will do all the calculations in a single cell.

Rolling Average
	A	B	C
1	1		4.8
2	0		6.4
3	5		5.8
4	4
5	0
6	6
7	8
8	0
9	0
10	9
11	0
12	2
Sheet6

Cell Formulas
Range	Formula
C1:C3	C1	=AVERAGE.ROLLING(A1:A12,5)
Dynamic array formulas.

=LAMBDA(range,group_size,
    LET(
        f,FILTER(range,range>0),
        sq,SEQUENCE(ROWS(f)-group_size+1),
        SCAN(
            0,
            sq,
                LAMBDA(
                    s,
                    c,
                    AVERAGE(
                        INDEX(
                            f,
                            SEQUENCE(group_size,,c)
                        )
                    )
                )
        )
    )
)