2

I have a table (the "Table"). Table does not start on the first row of the spreadsheet (there are blanks rows above Table). There are no blank items in Table. Table contains OtherColumn, by which it is sorted, and Column. 

OtherColumn    Column
a1             b1
a2             b2
...            ...
aN             bN

I want to find the average of the last N items in Column.

My current strategy involves =AVERAGE(OFFSET(M, -10, 0), M) where M is a cell reference to the last item in Column. How do I find M?

Prefer a non-VBA solution. Looking for a more elegant solution for M than:

CELL("address", OFFSET(Leaders[[#Headers],[Column]], COUNTA(B13:B1048576)-1, 0))

where C13 is the header row of the Table and C1048576 is the last row in Column B in the spreadsheet.

Felix
  • 831
  • 4
  • 12
  • 16

1 Answers1

4

Try this one:

=AVERAGE(INDEX(Table1[Column],ROWS(Table1[Column])-17):INDEX(Table1[Column],ROWS(Table1[Column])))

formula above gets average of last 18 elements in column Column.

Or using OtherColumn:

=AVERAGEIFS(Table1[Column],Table1[OtherColumn],">="&LARGE(Table1[OtherColumn],18))
Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80