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