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:
<tbody>
</tbody>
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>