On Wed, 15 Feb 2012 19:58:23 +0000, 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|
+-------------------------------------------------------------------+

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.