7 Day Average

[oskar14]

Hello,

I want to calculate the average of various numbers in a week. When drag and filling the formula, it should skip to the next week and calculate the average of the next 7 numbers. When I try to do it it only goes down one cell and I get a "moving" average you could say. How do I solve this?

Thanks in advance, 

Oskar

[#2]

@oskar14 

That has already been answered.

https://techcommunity.microsoft.com/t5/excel/drag-and-fill-problem/m-p/4084063

[#3 · Accepted solution]

@oskar14 

=MITTELWERT(INDEX($C$3:$C$23;1+(ZEILE(M1)-ZEILE($M$1))*7):INDEX($C$3:$C$23;7+(ZEILE(M1)-ZEILE($M$1))*7))

=AVERAGE(INDEX($C$3:$C$23,1+(ROW(M1)-ROW($M$1))*7):INDEX($C$3:$C$23,7+(ROW(M1)-ROW($M$1))*7))

This works if i correctly understand what you want to do.

[#4]

Thanks a lot it worked!

[#5]

@oskar14 

You could license 365 and simply turn your back on 'old style' spreadsheets once and for all!

The formula (in English) is

= BYROW(WRAPROWS(value, 7), AVERAGE)

"or away from insider beta"

= BYROW(WRAPROWS(value, 7), LAMBDA(x, AVERAGE(x)))