AVERAGEIFS for last X records in a customer list?

[GaryV]

I have a dataset that looks a bit like this:

Cust A	1/1/18	345
Cust A	2/1/18	634
Cust A	3/1/18	567
Cust B	11/1/18	1209
Cust B	12/1/18	1340
Cust B	1/1/18	1500
Cust B	2/1/18	1654
Cust B	3/1/18	1567
Cust C	2/1/18	423
Cust C	3/1/18	450
Cust D	3/1/18	1690

<tbody>
</tbody>

The record count can vary by customer. I'm looking to average the 3rd column's numbers, for up to the last 3 rows, by customer.

I have a separate list of unique customer ID's and I've been experimenting with AVERAGEIFS, Offset, etc., but can't seem to get the logic correct.

Could I kindly ask for an experts view on this and for any recommendations on a solution?

Many thanks!!
G

 

[GaryV]

Thanks Aladin, will give that one a go, too!

 

[Aladin Akyurek]

Thanks Aladin, will give that one a go, too!

Either you have the customer conditions in a separate range like below...

=AVERAGE(IF(ROW($C$2:$C$12)>=LARGE(IF($A$2:$A$12=F2,IF(ISNUMBER($C$2:$C$12),ROW($C$2:$C$12))),MIN(3,COUNTIFS($A$2:$A$12,F2))),IF($A$2:$A$12=F2,IF(ISNUMBER($C$2:$C$12),$C$2:$C$12))))

In G2 control+shift+enter, not just enter, and copy down:

=AVERAGE(IF(ROW($C$2:$C$12)>=LARGE(IF($A$2:$A$12=F2,IF(ISNUMBER($C$2:$C$12),ROW($C$2:$C$12))),MIN(3,COUNTIFS($A$2:$A$12,F2))),IF($A$2:$A$12=F2,IF(ISNUMBER($C$2:$C$12),$C$2:$C$12))))

Or like below where processing creates an additional range...

Row\Col	A​	B​	C​	D​
1​	customer	date	value	last 3 avg
2​	Cust A	1/1/2018​	345​	515.33​
3​	Cust A	2/1/2018​	634​	515.33​
4​	Cust A	3/1/2018​	567​	515.33​
5​	Cust B	11/1/2018​	1209​	1573.67​
6​	Cust B	12/1/2018​	1340​	1573.67​
7​	Cust B	1/1/2018​	1500​	1573.67​
8​	Cust B	2/1/2018​	1654​	1573.67​
9​	Cust B	3/1/2018​	1567​	1573.67​
10​	Cust C	2/1/2018​	423​	436.5​
11​	Cust C	3/1/2018​	450​	436.5​
12​	Cust D	3/1/2018​	1690​	1690​

In D2 control+shift+enter, not just enter, and copy down:

=IF(ISNA(MATCH($A2,$A$1:A1,0)),ROUND(AVERAGE(IF(ROW($C$2:$C$12)>=LARGE(IF($A$2:$A$12=$A2,IF(ISNUMBER($C$2:$C$12),ROW($C$2:$C$12))),MIN(3,COUNTIFS($A$2:$A$12,$A2))),IF($A$2:$A$12=$A2,IF(ISNUMBER($C$2:$C$12),$C$2:$C$12)))),2),VLOOKUP($A2,$A$1:$D1,4,0))

ROUND is added here for a nicer output.

 

[GaryV]

Thanks much, Aladin!! More to test!! I'll give this a go, too... many thanks!