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

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?

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

Excel says there are too many arguments in this function.

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, RO AY(19:1000)),4)):AY1000)

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

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


On Mon, 27 Feb 2012 19:38:15 +0000, puck1263
wrote:


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 ,RO
AY(19:1000)),4)):AY1000)


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

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

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.

That works. Thanks very much.

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

On Mon, 5 Mar 2012 22:06:27 +0000, puck1263 wrote:


That works. Thanks very much.


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

Glad to help. Thanks for the feedback.

Hello again -
I now have a variation on this.

What the above is looking at is looking for the average of the "last" 4 values in a column, (skipping blanks).
Those values happened to be averages of defects/month (# of defects in month/shipments in month) (See column E in attached spreadhseet...it looks at value in column D)

That's all fine and good, except (see example) the # of shipments greatly varies month to month, making this not the best representation.

What I would rather do is break up the avg calculation.
(If column B is blank, column C will always be 0 as defects are tied to the shipment in the month it shipped.)

Instead of (say all fields have a value as they do in this example) what I have now, which is AVG(D2:D5) or effectively: (C2/B2 + C3/B3 + C4/B4 + C5/B5)/4
I would rather have (C2+C3+C4+C5)/(B2+B3+B4+B5) where these are the last 4 rows that have values.
I have simulated this manually in column F.
Any idea how to do this?

I got it.

I changed the AVERAGE array formula provided above to SUM and made a column to sum numerator. I did same for denominator. Then took the average across the row of those two cells. (note - I had to insert an intermediate formula where if denominator is blank, numerator would be blank, not zero).

Hope this helps anyone else.