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 titles. There is no blank cells but there are zeros. I tried few ways to do this found on this and other forums but they all give me an error message "There´s a problem with this formula".
I don´t know why the functions don´t work (could it be caused by some wrong setting) but I would appreciate your advice and help.
I also tried =AVERAGE(OFFSET($G$2,(ROW()-ROW($I$2))*5,,5,)) -function and function
=AVERAGE(INDEX(G:G,2+5*(ROW()-ROW($I$2))):INDEX(G:G,1+3*(ROW()-ROW($I$2)+1)))
Below is a picture of one of the functions I tried (adapted from a post here about 12 row average). [The numbers in table are separated with commas, but that´s normal]
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)))
5 Replies
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.
I see you're using Excel 365 so there's no need for OFFSET.
Try something like this:
=LET(vector, TAKE(WRAPROWS(values, 5), , 1), AVERAGE(vector))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)))- Thank you so much this worked!!!!
And yes I meant average of set of 5 rows 🙂
