communiction log

[dmj120]

I'd like to create a communication log of several customers (at several accounts) which calculates the days since last date contacted.

This table will be a running log of customers. I've used a max() formula for finding the "last date" but with this, I'd need to find the last date and the 'next to last date' then subtract the two - I'm then going to average all the "days since last contacted" to get running average of how often a particular customer is contacted.

This is what I've used in the past to find the most recent date, but I'm stuck with looking back the closest previous date to subtract.
{=MAX(IF('SM-DM Jan2013-4.20-16'!$X$1:$X$13827=D15,'SM-DM Jan2013-4.20-16'!$M$1:$M$13827))}

any ideas or suggestions on how i can do this? There will eventually be a summary table with looks up the last entry for each facility/person so I can readily see the date last contacted, how many days since the last contact, and average time a particular customer is contacted --- there's some administrative needs behind this.

An example table:

Account	Person	Date Contacted	Days Since last contact	Average
Facility 1	Vickie	11/15/16
Facility 2	Bob	12/1/16
Facility 1	Vickie	12/1/16	16	16
Facility 1	Vickie	12/10/16	9	12.5
Facility 2	Bob	12/10/16	9	9

<tbody>
</tbody>

 

[FDibbins]

Change the MAX to LARGE

 

[dmj120]

Change the MAX to LARGE

Thanks, I'll play around with it