I cell B12 I want to display the average value of a column of numbers. The first number is in cell B27. The last number is in cell Bxx. The number of rows is periodically increased (as more data is added). So, obviously, the average value will change over time.
I am using the latest version of LibreOffice.
What formula should I enter in cell B12?
If there are no more data after Bxx: =AVERAGE(B27:B1048576). But you would not be abble to insert more rows (above or after) to the spreedsheet.
If there are more data after Bxx: =AVERAGE(B27:Bxx). And in Bxx you should add a reminder like “Add data above this row”. Then when you fill the cells until xx-1, insert more rows or cells above Bxx.
Thanks LeroyG. I should have realised this myself.
I entered the formula "=AVERAGE(B27:B999).
Since my highest (last) row number is less than 100, I have plenty of room to add more data (more rows) without modifying the formula. The formula ignores empty cells.
If you insert rows anywhere between B27:B999 the reference is automatically adjusted.
Yes, but geoffL said (in the previous comment) that never will reach that number of rows. So, no need to insert.
The text reminder I suggest could be replaced with a background color (in B27:B999).