Average separated array

[Jabe]

Hi,

Please see attached example. In the numbered columns I will put the marks for completed assignments. I would like to create a rolling average of the last 5 assignment scores. However, I need to keep the Intervention and Notes columns after each block of 5 assignments for a separate utility. Is there a way to average the last 5 values ignoring the Intervention and Notes columns?

 

[hagia_sofia]

Hello, would this work for you:

=IFERROR(AVERAGE(TAKE(FILTER(C3:T3,(ISNUMBER($C$2:$T$2)=TRUE)*(ISBLANK(C3:T3)=FALSE)),,-5)),"")

 

[Fluff]

How about

=AVERAGE(TAKE(TOROW(CHOOSECOLS(B3:T3,XMATCH(SEQUENCE(,15),$B$2:$T$2)),1),,-5))

 

[Jabe]

Hi hagia_sofia - yes that appears to work, thanks very much!

 

[hagia_sofia]

Thanks for the feedback!

 

[Peter_SSs]

Hi hagia_sofia - yes that appears to work,

I assume though you changed the ranges to start at column B, not column C?

If interested, that formula can be shortened to

=IFERROR(AVERAGE(TAKE(FILTER(B3:T3,ISNUMBER(B$2:T$2)*(B3:T3<>""),""),,-5)),"")

I'm also not sure you need the IFERROR wrapping as that would mean you didn't have any data at all to average for that row.

 

[felixstraube]

Another option:

=AVERAGE(TAKE(TOROW(HSTACK(B3:F3,I3:M3,P3:T3),1),,-5))

 

[Jabe]

I assume though you changed the ranges to start at column B, not column C?

If interested, that formula can be shortened to

=IFERROR(AVERAGE(TAKE(FILTER(B3:T3,ISNUMBER(B$2:T$2)*(B3:T3<>""),""),,-5)),"")

I'm also not sure you need the IFERROR wrapping as that would mean you didn't have any data at all to average for that row.

Thanks Peter. When I used the version you suggested I get a blank cell - could that be because there are less than 5 values in my array or is there something else?

 

[Jabe]

Thanks for the feedback!

I'm not entirely sure what happened, but when I tried to drag-copy the formula to other rows some of the results were (very) wrong (when checked manually) while others worked - so not sure if this formula works when copying for a larger dataset

 

[Peter_SSs]

When I used the version you suggested I get a blank cell - could that be because there are less than 5 values in my array or is there something else?

Must be something else - could be that the 'numbers' in row 2 (or at least some of them) are not actually numbers but text values.
Anyway, you have a solution that does not depend on that.