Ask Your Question
0

Average of last x rows in Calc? [closed]

asked 2012-12-03 09:55:59 +0200

O gravatar image

In a spreadsheet to which new rows are added continually, is there a way to write a formula that'll automatically calculate the average (or anything else, for that matter) of the values in the last x rows of a specific column?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-19 12:58:41.308289

2 Answers

Sort by » oldest newest most voted
0

answered 2012-12-04 08:38:50 +0200

JohnSUN gravatar image

You can define last X cells as

OFFSET($A$1;COUNTA($A$1:<any big address at column A>)-X+1;0;X;1))

Therefore, the desired formula will be similar to this design (for X=10)

=AVERAGE(OFFSET($A$1;COUNTA($A$1:$A$35000)-9;0;10;1))

Of course, this formula will work correctly only if all the cells in column A has a value (no missing data, empty cells)

edit flag offensive delete link more
0

answered 2012-12-12 18:17:33 +0200

O gravatar image

Belated thanks for your very useful answer.

edit flag offensive delete link more

Question Tools

Stats

Asked: 2012-12-03 09:55:59 +0200

Seen: 1,600 times

Last updated: Dec 12 '12