Mar 14 2024 08:43 AM
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
Mar 14 2024 08:54 AM
That has already been answered.
https://techcommunity.microsoft.com/t5/excel/drag-and-fill-problem/m-p/4084063
Mar 14 2024 09:03 AM
Solution=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.
Mar 17 2024 07:21 AM
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)))
Mar 14 2024 09:03 AM
Solution=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.