AVERAGEIFS for last X records in a customer list?

[GaryV]

I have a dataset that looks a bit like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cust A[/TD]
[TD]1/1/18[/TD]
[TD]345[/TD]
[/TR]
[TR]
[TD]Cust A[/TD]
[TD]2/1/18[/TD]
[TD]634[/TD]
[/TR]
[TR]
[TD]Cust A[/TD]
[TD]3/1/18[/TD]
[TD]567[/TD]
[/TR]
[TR]
[TD]Cust B[/TD]
[TD]11/1/18[/TD]
[TD]1209[/TD]
[/TR]
[TR]
[TD]Cust B[/TD]
[TD]12/1/18[/TD]
[TD]1340[/TD]
[/TR]
[TR]
[TD]Cust B[/TD]
[TD]1/1/18[/TD]
[TD]1500[/TD]
[/TR]
[TR]
[TD]Cust B[/TD]
[TD]2/1/18[/TD]
[TD]1654[/TD]
[/TR]
[TR]
[TD]Cust B[/TD]
[TD]3/1/18[/TD]
[TD]1567[/TD]
[/TR]
[TR]
[TD]Cust C[/TD]
[TD]2/1/18[/TD]
[TD]423[/TD]
[/TR]
[TR]
[TD]Cust C[/TD]
[TD]3/1/18[/TD]
[TD]450[/TD]
[/TR]
[TR]
[TD]Cust D[/TD]
[TD]3/1/18[/TD]
[TD]1690[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][/tr][tr][td]

1​

[/td][td]customer[/td][td]date[/td][td]value[/td][td]last 3 avg[/td][/tr]
[tr][td]

2​

[/td][td]Cust A[/td][td]

1/1/2018​

[/td][td]

345​

[/td][td]

515.33​

[/td][/tr]
[tr][td]

3​

[/td][td]Cust A[/td][td]

2/1/2018​

[/td][td]

634​

[/td][td]

515.33​

[/td][/tr]
[tr][td]

4​

[/td][td]Cust A[/td][td]

3/1/2018​

[/td][td]

567​

[/td][td]

515.33​

[/td][/tr]
[tr][td]

5​

[/td][td]Cust B[/td][td]

11/1/2018​

[/td][td]

1209​

[/td][td]

1573.67​

[/td][/tr]
[tr][td]

6​

[/td][td]Cust B[/td][td]

12/1/2018​

[/td][td]

1340​

[/td][td]

1573.67​

[/td][/tr]
[tr][td]

7​

[/td][td]Cust B[/td][td]

1/1/2018​

[/td][td]

1500​

[/td][td]

1573.67​

[/td][/tr]
[tr][td]

8​

[/td][td]Cust B[/td][td]

2/1/2018​

[/td][td]

1654​

[/td][td]

1573.67​

[/td][/tr]
[tr][td]

9​

[/td][td]Cust B[/td][td]

3/1/2018​

[/td][td]

1567​

[/td][td]

1573.67​

[/td][/tr]
[tr][td]

10​

[/td][td]Cust C[/td][td]

2/1/2018​

[/td][td]

423​

[/td][td]

436.5​

[/td][/tr]
[tr][td]

11​

[/td][td]Cust C[/td][td]

3/1/2018​

[/td][td]

450​

[/td][td]

436.5​

[/td][/tr]
[tr][td]

12​

[/td][td]Cust D[/td][td]

3/1/2018​

[/td][td]

1690​

[/td][td]

1690​

[/td][/tr]
[/table]

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!