Forum Discussion
7 Day Average
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
=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 Replies
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)))=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.
- Thanks a lot it worked!
That has already been answered.
https://techcommunity.microsoft.com/t5/excel/drag-and-fill-problem/m-p/4084063
