average the 6 most recent dates

Question

Column A has a list on various date entries, and column b has a list on numerical entries that correspond to the dates in column A. I want to use a formula to average the most recent 6 entries (according to the date) in cell C1 and another formula to add the most recent 6 entries (according to the date) in cell D1. Is this possible? Thanks.

Answer 1

... what if I wanted to average the 30 most recent entries from column B by date with the same scenario already mentioned and also add the 30 most recent entries from column B by date ...

You can hard code the 30 into the formula as you mentioned or put it into a cell and reference the cell. Let's assume that you put 30 into Z1. Your two formulas would be,

=AVERAGEIF(A:A,">="&LARGE(A:A,MIN(Z1,COUNT(A:A))),B:B)

... and,

=SUMIF(A:A,">="&LARGE(A:A,MIN(Z1,COUNT(A:A))),B:B) 

You may want to tighten up those full column references (e.g. A:A, B:B to A2:A999, B2:B999) to save some processing power. You can reference cells that are blank or contain text and they will be happily skipped over byt the SUMIF() and AVERAGEIF() functions. I've added a MIN(COUNT()) function to make sure that you are not asking for more values than are available.

AVERAGEIF function
SUMIF function
LARGE function
MIN function
COUNT function

Answer 2

Mike's great formula seems to find the average of the 6 oldest entries; I read your question as needing the 6 newest entries. Without checking for blanks, this seems to work =AVERAGE(IF(A1:A20>=LARGE(A1:A20,6),B1:B20))

NOTE is an array formula, so use CTRL+SHIFT+ENTER to commit it.

best wishes

Answer 3

.. use a formula to average the most recent 6 entries (according to the date) in cell C1 and another formula to add the most recent 6 entries (according to the date) in cell D1 ... 

You haven't mentioned whether there is a possibility of duplicates dates within the "" , nor how to deal with that eventuality. Given that an AVERAGE() can be deconstructed to SUM()/COUNT() see if these will produce acceptable results (dates in A1:A20 and values in B1:B20).

SUM() 6 most recent

=SUMPRODUCT((B1:B20)*(A1:A20>=LARGE(A1:A20,6)))

COUNT() 6 most recent (including duplicates)

=SUMPRODUCT(--(A1:A20>=LARGE(A1:A20,6)))

AVERAGE() 6 most recent (adjusting for duplicates)

=SUMPRODUCT((B1:B20)*(A1:A20>=LARGE(A1:A20,6)))/SUMPRODUCT(--(A1:A20>=LARGE(A1:A20,6)))

... or,

=AVERAGEIF(A1:A20,">="&LARGE(A1:A20,6),B1:B20)

When guarding against duplicates, the biggest worry is a duplication of the 6thg most recent which would create 7 matching entries given the above formulas.

Answer 4

Hi,

Try this ARRAY formula and see below on how to enter it which works if the dates are in a random order. Change the 20 to capture all the data. It doesn't matter if the date range contains blanks, these will be ignored.

=AVERAGE(IF(A1:A20>=LARGE(A1:A20,6),B1:B20))

This is an array formula which must be entered by pressing CTRL+Shift+Enter

and not just Enter. If you do it correctly then Excel will put curly brackets

around the formula {}. You can't type these yourself. If you edit the formula

you must enter it again with CTRL+Shift+Enter.

EDIT Thanks to a comment from Bernard Liengme formula edited

Answer 5

Assuming the data is listed in order of dates (most recent at the bottom) and the first data point is in A1:B1,

then =SUM(OFFSET(B1,COUNT(B1:B200),,-7)) will sum the last 6

and =SUM(OFFSET(B1,COUNT(B1:B200),,-7))/6 will give the average

Adjust the 200 as needed to cover the entire data set.

But if the dates are in random order we have a very different problem

best wishes