Moving Average Best 5 of last 6 values

[jimrward]

I have a rows of data which contain variable numbers of columns, some with values some without.

What i need to do is take the average of the best 5 from the last six non zero values, providing there are more than 5 columns, if there are only 5 then just the average.

 

[Domenic]

=AVERAGE(LARGE(IF(COLUMN(A1:I1)>=LARGE(IF(A1:I1>0,COLUMN(A1:I1)),6),IF(A1:I1>0,A1:I1)),{1,2,3,4,5}))

Hi, Domenic. I think this fails (#NUM! error) if there are fewer than 6 non-zero numbers. The "providing there are more than 5 columns, if there are only 5 then just the average" part of the OP's request led me to the beast I posted above in event there were rows where there were fewer than 6 (but >=1) cases...

Hi Oaktree!

Thanks for pointing that out. I really appreciate it. I missed it completely. In this case, maybe my formula can be corrected as follows...

=AVERAGE(LARGE(IF(COLUMN(A1:I1)>=LARGE(IF(A1:I1>0,COLUMN(A1:I1)),MIN(6,COUNTIF(A1:I1,">0"))),IF(A1:I1>0,A1:I1)),{1,2,3,4,5}))

or

=AVERAGE(LARGE(IF(COLUMN(A1:I1)>=LARGE(IF(A1:I1>0,COLUMN(A1:I1)),MIN(6,COUNTIF(A1:I1,">0"))),IF(A1:I1>0,A1:I1)),ROW(INDEX(A:A,1):INDEX(A:A,COUNTIF(A1:I1,">0")))))

Note that the first formula will only return an average when there's at least 5 non-zero numbers, whereas the second one will only return an average when there's at least one non-zero number.

Hope this helps!

 

[jimrward]

Hi all

remember this cracking formula you came up with for best 5 out of 6, when there are no values it gives #ref! error, i have a cell which counts the values address is S8, how can i change the formula to include a condition such as
=if( s8>0, arrayformula,0)

{=SUM(LARGE(INDIRECT("R"&ROW()&"C"&LARGE(((D8:Q8>0)*COLUMN(D8:Q8)),ROW(INDIRECT("1:"&MIN(COUNTIF(D8:Q8,">0"),6)))),0),ROW(INDIRECT("1:"&MIN(COUNTIF(D8:Q8,">0"),5)))))/MIN(COUNTIF(D8:Q8,">0"),5)}

 

[Oaktree]

Why not just put the array formula in T8 and use:

=IF(S8>0,T8,0) or similar?

 

[jimrward]

that is the route I took in the end, as anything else i tried with the array formula ended in tears due to its overwhelming complexity for my small brain.