calculate last 4 data points in a column (with sometimes blank rows)

[puck1263]

Hello-
I'd like a formula to place the average of the last 4 data point
(sometimes there will be blanks) in a column and display that value i
another column.

By "last 4", I mean last 4 entries in the row selected, or above. Se
attachment.

So-
I would like to put, in column BA, the average of the last 4 entries i
column AY, for that row and above. I'd like to be able to copy tha
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
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 i
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
+-------------------------------------------------------------------

 

[Don Guillett]

Modify to suit a to ay and 10 to 4

array formula (CRTL+Shift+Enter):
=SUM(INDIRECT("A"&LARGE(IF(ISBLANK(A2:A10000),0,ROW(2:10000)),
10)):A10000)

 

[puck1263]

Excel says there are too many arguments in this function

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[puck1263]

So, I looked at this again and must have copied something wrong.

Now I get the #NAME? error all the way down the column.

Here is what I have in the first cell (row 19)
SUM(INDIRECT("AY"&LARGE(IF(ISBLANK(AY19:AY1000),0,R
AY(19:1000)),4)):AY1000

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Gord Dibben]

I see a couple things wrong with your formula as posted but might be
typos or just the way you copied it.

Try this based on Don's formula edited.

=SUM(INDIRECT("AY"&LARGE(IF(ISBLANK(AY19:AY10000),0,ROW(19:10000)),4)):AY10000)



Gord

 

[Ron Rosenfeld]

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|
+-------------------------------------------------------------------+

This formula must be **array-entered**. Then select and fill down as far as required:

BA36:
=AVERAGE(IF(ROW($AY$1:AY36)=LARGE(
ISNUMBER($AY$1:AY36)*ROW($AY$1:AY36),
{1,2,3,4}),$AY$1:AY36))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

 

[puck1263]

That works. Thanks very much

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Ron Rosenfeld]

That works. Thanks very much.


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+

Glad to help. Thanks for the feedback.