Forum Discussion
Solved
Problem with every 5 row average function
Hello! I have a very large table (over 45000 rows so manual calculation is not an option) and I want to calculate the averages for every 5 rows. My data starts at row 2 and the first row is title...
Patrick2788 I read the request slightly differently and that they need to average every set of 5 rows (1-5, 6-10, etc...) so I suggest this:
=BYROW(WRAPROWS(values, 5),LAMBDA(vector, AVERAGE(vector)))
Your formulas work as intended in my sheet. The OFFSET formula is in cell I2 and filled down and the INDEX formula is in cell H2.
=AVERAGE(IF(MOD(ROW($F$2:$F$26)+3,5)=0,$F$2:$F$26))This formula is in cell G2 and it's almost the formula from your screenshot. The formulas have to be entered with ctrl+shift+enter if one doesn't work with Office 365 or Excel 2021.