calculate last 4 data points in a column (with sometimes blank rows)
Modify to suit a to ay and 10 to 4
array formula (CRTL+Shift+Enter):
=SUM(INDIRECT("A"&LARGE(IF(ISBLANK(A2:A10000),0,RO W(2:10000)),
10)):A10000)
On Feb 15, 1:58*pm, puck1263 wrote:
Hello-
I'd like a formula to place the average of the last 4 data points
(sometimes there will be blanks) in a column and display that value in
another column.
By "last 4", I mean last 4 entries in the row selected, or above. *See
attachment.
So-
I would like to put, in column BA, the average of the last 4 entries in
column AY, for that row and above. *I'd like to be able to copy that
formula down the row so I can get a snapshot for each month.
So for instance, cell BA36 should have the average of the lowest 4
values in column AY, from row 36 and up (result should be 1.29).
Likewise, cell BA54 should get the average of the lowest 4 entries in
column AY, from row 54 an up (result should be *average of cells AY47,
AY47, AY49,AY51=1.89).
Any help please?
+-------------------------------------------------------------------+
|Filename: excel question.JPG * * * * * * * * * * * * * * * * * * * |
|Download:http://www.excelbanter.com/attachment.php?attachmentid=295|
+-------------------------------------------------------------------+
--
puck1263
|