0

Newbie here. This question is about spreadsheets (Excel / LibreOffice Calc / Google Sheets ...).

I have a dataset in a column. And I want to average all the non-empty values of the column except for the very last.

Example: Dataset is 1, 2, 3, 4, 5, 6, 7, 8 and I want to average everything minus the 8.

The formula I'm looking for should be kind of "dynamic", because if tomorrow I add data point 9, I want the formula to average everything 1 to 8, ignoring 9, keeping track of all the column.

How do I do that? I've tried several things, googled extensively and I can't find anything.

EDIT: Also, the SUM of all points minus the last.

Thanks to whoever might help.

Alex

player0
  • 124,011
  • 12
  • 67
  • 124
alexem82
  • 1
  • 2
  • 1
    There is no guarantee that a solution in one of Excel / Sheets / LibreOffice Calc will work in the other(s). Which of the three are you *actually* using? – BigBen Dec 02 '21 at 21:36
  • Do you want to average the non-empty values in a column except for the _last_ one, or except for the _highest_ one? – cybernetic.nomad Dec 02 '21 at 21:38
  • @BigBen I'm using Google Sheets but I have never encountered a function that worked in one but not the other (yet...) – alexem82 Dec 02 '21 at 22:14
  • @cybernetic.nomad only the LAST one before empty cell. – alexem82 Dec 02 '21 at 22:15
  • 1
    I can list many. for example, query, split, regexmatch are all in sheets and not in the others. While Aggregate and the ability to use INDEX as an address all work in Excel but not the others. There are more differences than one would expect. – Scott Craner Dec 02 '21 at 22:18

1 Answers1

1

use:

=AVERAGE(ARRAY_CONSTRAIN(FILTER(A:A, A:A<>""), COUNTA(A:A)-1, 1))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124