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.
[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. °/| >
“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.