# Calc average column value

I would like to know how to calculate the average values of a column (no matter how long it is) but ignoring the very top cell (title). I know the AVERAGE function but I am not figuring how to:

a) Tell the program to include ALL rows of the columns (currently I am extrapolating the total of columns but from times to times I have to update it as the column grows); and

b) Tell the program to exclude the top one cell (title of the column).

Thanks a lot!

And what is in the top cell? (The answer depends on that.)

If the title is text it will -as any text- be ignored by AVERAGE() anyway. To average all of column A you can therefore use `=AVERAGE(A:A)` (if not your version is very old and not yet ready to accept the whole-column-reference). You needn’t be afraid of inefficiency in this case. Calc is bookkeeping about empty ranges.

… unless it’s the first cell where the average should appear - in which case, SUBTOTAL would help

[I never recommend SUBTOTAL(). Probably I didn’t understand that great feature from beforethetimes.]
Using the first cell for a result is ‘ok’ under certain conditions. I wouldn’t call it a title, howvever, in this case. °/| >

@Lupp

“I never recommend SUBTOTAL(). Probably I didn’t understand that great feature from beforethetimes.”

The `AGGREGATE()` function is amazing for getting data from cells that use functions like `SUBTOTAL()` without including duplicate cells which have been accounted for.

It’s one of my favorite functions for financial spreadsheets because I don’t have to worry about functions or grouping giving me incorrect data.

Just make sure you pass the correct args to `AGGREGATE()`, it is a very robust function with different ways to analyze data.

Make sure the title -as also intertitles- of your column, say A, are text (meaning neither directly entered numbers nor numbers returned by formulas).
`=AVERAGE(A:A)` is the formula you need then.
If the first row needs to be excluded explicitly for very special reasons, =AVERAGE(A2:A1048576) is also ok.

Well `=AVERAGE(A:A)` didn’t work for me (although the very first cell -A1- is pure text).

It shows the result as `#NAME?`

But `=AVERAGE(A2:A1048576)` did the trick!

Thanks!

You may consider to update your LibreOffice. Since V5.0.0 (released 2015-08-03) Calc supports the full-row and the full-column references. There are many additional bugfixes and enhancements, and some of them are really valuable. Current “fresh” version is 6.4.0.
V 6.3.3 should be a sufficiently matured “still” version.