Automatically Counting Rows

[McP]

Hello,

I’d like to divide a sum by the amount of rows above it.

Example (to explain this better):

A2…A15 contains cells with an amount of working hours.
A17 contains a formula: =SUM(A2:A15)/14) to calculate the average.
A15 contains a dummy figure so I can add a line above A15 and it will be included in the sum automatically.
But I have to count the rows manually and adjust the formula every time and would like to replace the number (14) with a formula.

Is there a way to just count the number of rows automatically and include it?

[Lupp]

Obvious question in return: What rows? (What criterion qualifies any row to be counted?)
Answer taking the question as literal as reasonable regarding the full text: =ROWS(A$2:A15) or =ROWS(A$2:A15) - 1 if the “dummy figure” needs to be excluded (conflicting with the 14 of the example).
Answer regarding the obvious educated guess concerning the actual intentions of the questioner:
=AVERAGE(A$2:A15). See also Opaque’s answer.

@McP a request: Post a comment making clear what actually was the intention, and which attempted answer (if any) showed the way to “best practice”.

Neither forums nor Q&A sites are oneway.

[Wanderer]

I usually use COUNTIF, but there are other options,
as this older answer shows: ask.libreoffice

J.

PS: Take care: Some cells are really empty,
some may contain a formula with value “”, and some may have a value of 0. You have to decide, wich approach works for you.

[anon73440385]

Hello,

just use =AVERAGE(A2:A15) - assure that A15 is not a numerical value.

[McP]

Thank you!
The AVERAGE option is exactly what I was looking for, much appreciated.

However, I’ve got issues using it with this forrmula: =AVERAGE((J8:J19)+(L6:L17))
It returns “#VALUE!”

[anon73440385]

Use: =AVERAGE(J8:J19;L6:L17) - see also LibreOffice Help - AVERAGE