Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenWed, 12 Dec 2012 18:17:33 +0100Average of last x rows in Calc?https://ask.libreoffice.org/en/question/8556/average-of-last-x-rows-in-calc/In a spreadsheet to which new rows are added continually, is there a way to write a formula that'll automatically calculate the average (or anything else, for that matter) of the values in the last x rows of a specific column?Mon, 03 Dec 2012 09:55:59 +0100https://ask.libreoffice.org/en/question/8556/average-of-last-x-rows-in-calc/Answer by JohnSUN for <p>In a spreadsheet to which new rows are added continually, is there a way to write a formula that'll automatically calculate the average (or anything else, for that matter) of the values in the last x rows of a specific column?</p>
https://ask.libreoffice.org/en/question/8556/average-of-last-x-rows-in-calc/?answer=8590#post-id-8590You can define last X cells as
OFFSET($A$1;COUNTA($A$1:<any big address at column A>)-X+1;0;X;1))
Therefore, the desired formula will be similar to this design (for X=10)
=AVERAGE(OFFSET($A$1;COUNTA($A$1:$A$35000)-9;0;10;1))
Of course, this formula will work correctly only if all the cells in column A has a value (no missing data, empty cells)Tue, 04 Dec 2012 08:38:50 +0100https://ask.libreoffice.org/en/question/8556/average-of-last-x-rows-in-calc/?answer=8590#post-id-8590Answer by O for <p>In a spreadsheet to which new rows are added continually, is there a way to write a formula that'll automatically calculate the average (or anything else, for that matter) of the values in the last x rows of a specific column?</p>
https://ask.libreoffice.org/en/question/8556/average-of-last-x-rows-in-calc/?answer=8983#post-id-8983Belated thanks for your very useful answer.Wed, 12 Dec 2012 18:17:33 +0100https://ask.libreoffice.org/en/question/8556/average-of-last-x-rows-in-calc/?answer=8983#post-id-8983